Queries to search for two variables, based on user input

[jondavidf]

Hi all!

This is what my goal is: Create a query that prompts a user for a date, then counts the number of personnel who occupy a specific building during a specific time period.

This is what I have so far (all data is from the "DATABASE MAINFRAME" table):





1 - The first visible field of "Todays Count" accurately displays the present number of occupants in building 123. Everything is working as it should.

2 - The second visible field of "Next Weeks Count" accurately counts all those graduating next week, however, it does not filter the address to "123" as "Todays Count" does. The grad date count works but the address part does not.

3 - I would like to remove the "Date()" value from "Next Weeks Count" and have the date based off user input but I'm not sure how to do that. I tried creating another field such as [ENTER DATE] and have the query reference that date but I couldn't get that to work. Once I get the formula worked out I'm going to add more fields to project the number of graduates from building 123 for the next 6 weeks.

P.S. I know the naming convention is not good and I'll address that shortly.

Thanks for all the help!

Jon

[June7]

Review: http://datapigtechnologies.com/flash...mtoreport.html

[jondavidf]

I reviewed the video you sent me and was able to get myself much closer to the desired output. I created a form as described in the video where I could use a combo box to enter the address and a combo box/calendar to enter the date.

I linked the query to the form and all is well - almost. This is what I have that does work:

When I try to add a count for week 1 I added a field of [Week 1] in Column 4, added a modified [GRAD DATE] criteria in Column 5, and repeated the [ADDRESS] field in Column 6 as such:



This DOES NOT WORK correctly. I know it has something to do with having two [GRAD DATE] columns and two [ADDRESS] columns. Access is merging the like columns.

How do I, then, make the [Start Date Count] field only search the criteria outlined in Columns 2 & 3 and make the [Week 1] field only search the criteria outlined in Columns 5 & 6? Eventually I'd like to add columns for Week 2, Week 3, Week 4, Week 5, and Week 6.

Thanks for all the help!!

[June7]

What LIKE columns? I don't see LIKE and wildcard used in the criteria.

Try:

Between Nz([Forms]![BARRACKS Utilization]![cboBarracksReportDate],#1/1/1900#) And Nz([Forms]![BARRACKS Utilization]![cboBarracksReportDate],#1/1/1900#)+13 Or Is Null

LIKE Nz([Forms]![BARRACKS Utilization]![MyAddress] & "*" Or Is Null

[jondavidf]

Thanks for the quick response! I'm not sure exactly what you mean, though. Do the Between and Like statements go in the Start Date Count and Week 1 criteria? Do I still use the Grad Date and Address Columns?

[June7]

Those would be criteria under Start Date and Address columns, but only one of each.

I don't understand the Count("*"). That is an aggregate function and must be used in a GROUP BY query.

I never use query parameter input prompts. Can't validate user input. Better to use reference to form textboxes as parameter input.

[jondavidf]

Those would be criteria under Start Date and Address columns, but only one of each.

I don't understand the Count("*").

The purpose of the query is to count the records that meet the [Address] and [cboBarracksReportDate] criteria. It is used to show how many people will occupy the building on the chosen date. The [Week 1], [Week 2], etc. fields will be used to show how many people are leaving each week from the chosen start date, ie (Between([cboBarracksReportDate]+7) And ([cboBarracksReportDate]+13)). It will be used to show at what current level the building is being utilized and what the projected losses are.

For example, I want the query to show that 148 people occupy building "123" on 9/16/2013. The week of 9/23 - 9/29 there will be 12 people leaving. The following week of 9/30 - 10/6 there are 8 people leaving... all the way to six weeks out.

That is an aggregate function and must be used in a GROUP BY query.

I'm not sure about the GROUP BY query, but I believe the aggregate function is what I'm looking for. All I need is the count, not the list of the actual records that meet the criteria.

I never use query parameter input prompts. Can't validate user input. Better to use reference to form textboxes as parameter input.

Awesome idea to use the combo boxes/text boxes. Incorporating a pop-open calendar to choose the date is very helpful, as well.

Thanks again for all the help!!!

[June7]

Access Help has guidelines on building GROUP BY query (use the Totals button on the ribbon).

[jondavidf]

Solved using these formulas with a GROUP BY query as you recommended:

Start Date Count: DCount("[SSN]","[DATABASE MAINFRAME]","([GRAD DATE]>=([Forms]![BARRACKS UTILIZATION]![cboBarracksReportDate])Or([GRAD DATE] Is Null))And([ADDRESS]=[Forms]![BARRACKS UTILIZATION]![MyAddress])")

and

Week 1: DCount("[SSN]","[DATABASE MAINFRAME]","([GRAD DATE]Between([Forms]![BARRACKS UTILIZATION]![cboBarracksReportDate]+7) And ([Forms]![BARRACKS UTILIZATION]![cboBarracksReportDate]+13))And([ADDRESS]=[Forms]![BARRACKS UTILIZATION]![MyAddress])")

For weeks 2, 3, etc. I just modified week 1 to add 14 and 20, etc.

THANKS again for all your help!!!