X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Need Help Editing My Code - Find Nightly Occupancy By User, Access 2016    
post Feb 12 2019, 03:33 AM

Posts: 11
Joined: 3-November 18


I have the below code to tell me the occupancy each night. How would I edit this to show me the nightly occupancy by user who admitted them? I'm able to get each an individual user by adding a statement to the WHERE clause (WHERE user = [user name]) but I can't get it to list out all values for all users each night.

SELECT Import_CalendarDate.CalendarDate, Count(ID) AS TotalOccupancy

FROM Import_CalendarDate LEFT JOIN 400_qry6_MRNOccupancy ON (Import_CalendarDate.CalendarDate>=[400_qry6_MRNOccupancy].Date_Admitted) AND (Import_CalendarDate.CalendarDate<[400_qry6_MRNOccupancy].Date_Discharged_U)

WHERE Import_CalendarDate.CalendarDate<date()

GROUP BY CalendarDate

ORDER BY Import_CalendarDate.CalendarDate;

Thanks in advance!
Go to the top of the page
post Feb 12 2019, 04:02 AM

Posts: 427
Joined: 25-January 16

Perhaps add user to the SELECT and GROUP BY clauses. Or instead of grouping in query, build a report and use its Sorting & Grouping features to organize records. This will allow display of detail data as well as do aggregate calcs.
This post has been edited by June7: Feb 12 2019, 04:05 AM

To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post Feb 14 2019, 02:04 AM

Posts: 11
Joined: 3-November 18

Worked like a charm! Thanks. Forgot to include the user in the group by.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th April 2019 - 07:43 AM