Hello, I am trying to create a r

[Aspen77]

Hello,

I am trying to create a report that requires the following data for a one week period:
Date of Stay =[Forms]![FormName]
Week Day=WeekdayName(Weekday([Forms]![FormName]![txtStart])) (Takes the date input by user then converts it to day of week and then I added plus 1,2. etc to get the full week it worked not sure if this is the best way, if not please point me in the right direction)
Overnight Day Count
Overnight YTD Count
Overnight YTD Count Grand Total
Dinner Only Day Count
Dinner Only YTD Count
Dinner Only YTD Count Grand Total

The fields I have in this table are:
Guest Name
Attendance Date
Overnight/DinnerOnly
CheckOutDate

I just started working on this report and I am using subreports(this will require about 28 subreports, is this the correct method or is there a better solution? Also, if all the info is from the same table can I put it all in one query or is there a better solution?

Thank you!

 

[dhookom]

Welcome to Tek-Tips.

Can we assume the Overnight/DinnerOnly is a yes/no field?
Would it be possible to enter some sample records and then how you want the data to display in the report?

I can't imagine why you would need multiple queries or even subreports.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Awesome, thank you for responding!

The Overnight/DinnerOnly field is a selection field "Overnight" or "Dinner Only"

In an effort to complete the report I created multiple queries and subreports it is working for the Overnight Day Count, Dinner Only Day Count and total Counts for each. I am still working on the YTD Counts. I would like to get rid of all those queries and subreports if possible and use fewer queries and subreports.

User inputs Start Date 09/01/2012 End Date 09/07/2012 (This is a one week report)

Query for DinnerOnlyDay0:
SELECT Count(*) AS [Count]
FROM [Guest Attendance]
WHERE [Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart;

Query for DinnerOnlyDay1:
SELECT Count(*) AS [Count]
FROM [Guest Attendance]
WHERE [Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart+1;

and then I created queries for the rest of the week by adding plus 1,2, etc. I also created queries for the overnight counts(more queries)

Table Attendance
Guest Name = Martinez, Samantha
Attendance Date= 09/01/2012
Overnight/DinnerOnly = DinnerOnly
CheckOut = 09/01/2012

Guest Name = Smith, Mark
Attendance Date= 09/01/2012
Overnight/DinnerOnly = Overnight
CheckOut =

Report Sample
Date of Stay 09/01/2012
Week Day Saturday
Overnight Day Count 94 (pulls subreport with Count)
Overnight YTD Count
Dinner Only Day Count 23 (pulls subreport with Count)
Dinner Only YTD Count

Date of Stay 09/02/2012
Week Day Sunday
Overnight Day Count 104 (pulls subreport with Count)
Overnight YTD Count
Dinner Only Day Count 27 (pulls subreport with Count)
Dinner Only YTD Count

Totals for each Count (Totals subreports for Overnight Day and Dinner Only Day is working, still working on YTD)

I hope this makes sense! Thanks again for any assistance you can provide!

 

[dhookom]

You can get a count in a report with a record source of Guest Attendance with an expression like:

=Sum(Abs([Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart))

For the next day

=Sum(Abs([Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart + 1))

You can substitute in other valus to get other counts.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

You are awesome! Great job! Thank you so much it worked great, now I can delete all those queries and subreports!

Next question, the record source was blank and when I changed it to Guest Attendance now I am receiving a row for every record in that table on the report, please let me know how I remove those rows. Thank you!

 

[dhookom]

It you don't want to see the detail records, either change the query to a totals query or place your controls in a group or report header/footer and set the detail height to 0.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Again, thank you so much it worked moving the controls to the Report Header with the details height set to 0. Just out of curiosity what would I have needed to change to use the totals query?

 

[dhookom]

It's a bit difficult without understanding all of your control sources however you can for instance take one of your control sources and use it like:

SELECT Sum(Abs([Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart + 1))  As DinnerOnlyPlus1
FROM ....

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Thank you!

 

[Aspen77]

I have another question on the same report. My reports were working great with the record source that you provided above =Sum(Abs([Overnight/Dinner Only]="Dinner Only" And [Attendance Date]=Forms!ICSGAttendanceForm!txtStart))

I was asked to include time, so I am using the now()function for the date field. In the Attendance tbl the Attendance date field now includes the date and time for new records that I added. The previous records only have the date. When I run the report it only selects the records that has the date only. It does not pick up he records with the date and time. This is a new database so I when it goes into production the Attendance Date records will all be with date and time.

Please let me know how I change the record source to include records with date and time. Thank you!

 

[dhookom]

I don't have a clue what you actually want to do. Do you want to remove the time portion from the field and then compare it to the txtStart value? If so, consider using the DateValue() function.

Duane
Hook'D on Access
MS Access MVP