Grouping on datetime fields restricts records

Using Crystal reports 8.5 with sql database odbc.

Have a report which must be grouped by an appointments datetime field.  When they entered the data they mistakenly entered two appts. at the same date and time.  Once generated they can not go back and change the entry.  The report is only showing one of the appts. not both.  Have group set to group on datetime field by seconds but they are exactly the same.  Both are 3/27/2006 10:30:00 am. If I group by patients ID number all correct records show but when grouping on datetime I loose one record. Have tried several things, no answer. Can someone help?
LBarrettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wykabryanCommented:
What is the column lay out look like?

Group Header 1 - Datetime
Name|Datetime |Description


The next question is why have the grouping at that level, since you want to see both records even if it was entered in error?
0
LBarrettAuthor Commented:
No columns after the header just a subreport.   They wanted to be able to see the times on the drilldown, expected to see 10:30 am and then drilldown further.  If not this way then how?
0
wykabryanCommented:
So on the main report they want to see 10:30, but then when they drill down into 10:30 they want to see both(if they exist, even in error) 10:30 appointments.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

LBarrettAuthor Commented:
Yes, that is one way for them to tell that the time is wrong. Sounds weird but since they can't change an appointment that has already been entered this is an error check for them.
0
wykabryanCommented:
Ok.. This should work with out a subreport.

Group on Datetime -
Then in the detail section provide the detail.  Right click on the Section Name, and choose hide (not suppress).

The way in the report it will appear as one line but one clicking of one of the datetimes it will display the details behind the scenes.

Hope this helps.
0
LBarrettAuthor Commented:
BTW datetime is a parameter for the main and all subreports as is Employee ID.  One employee can have many appts on the same day and can even see the same client twice on the same day.  Part of selection formula for main is:
{DocVstCtn.DatIn} in {?DateIn} to dateadd("h",23,{?DateIn}) and
{PatCht.AutPrsSys} = {?Personnel ID}
0
LBarrettAuthor Commented:
My fault it not being clear about the design of this report.  There is no detail on the main.  There are only page headers a & b with 3 groups below. Group Header 1a, 1b, 1c.  All are grouped by same datetime parameter field and linked to main with a document number.  All other sections on main are suppresed except for page footer a & b which are conditionally supressed based on page number. Does this help?
0
wykabryanCommented:
What information is contained in GH 1a, 1b, 1c?  What is the link look like between the main and the sub for appointments?

I am wondering if in the subreport if you reformated the time to get rid of the seconds if that would help.  A formula like in the main and subreport:

Time (datepart("h",{subreport time}),datepart("n",{subreport time}),00)

then modify the link between the main and sub to be based around the formula time.
0
LBarrettAuthor Commented:
Main Report is patient info:

Name

address

appt date, employee Id etc.
-------------------------------------------------------------------------
Group 1a -1st sub  - medical visit assessment info
-------------------------------------------------------------------------
Group 1b - 2nd sub - mental health questions and answers
-------------------------------------------------------------------------
Group 1c - 3rd sub - clinical notes info and advisements
-------------------------------------------------------------------------
all subs are linked to main with a unique document number assigned by the system software to the patient.

I did try to change the grouping to hour and lost even more patients. Those with appts. at say 10:15 am were gone.  As I said before if I group by document number all visits show but not in datetime order.
0
wykabryanCommented:
Well, there might be a way around that as far as displaying it and ordering it.  I believe this exist in 8.5

You could link on patienid
Then in the subreport do the following:
group by document number.  Then go to the options tab.

Check the first box, "Customize Group Name Field"
   leave it at the first option and change it to the datetime field.



Running out of ideas...hehehe..
0
LBarrettAuthor Commented:
Will give this a try and post results.  Will let you know.  Thanks for the help so far.
0
mlmccCommented:
Do you have SUPPRESS IF DUPLICATE set for the detail section or fields in the subreport?

Since the appointment times are the same you only get one group header  for the two records.

You could display the data in the DETAIL Section and allow drill down

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Glad i could help

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.