Run a query from 2 tables according to date - please help! :)

[supersezzie]

Hi, please forgive my newbieness! I'm learning fast and you guys are brilliant I have two tables and want to compile the data onto one query - can you help?

One table is a time monitor table with one record per date, with date as the primary key. It contains a number of fields where I enter the number of minutes spent on each task at the end of the day.

The second table is an appointment log. It can have multiple records for each date and the records contain a number of fields with info about the appt, one of which is the length of appt.

I have set up a relationship between the date field in both tables (was this necessary?). Is it possible to set up a query that will sort by date and grab the data from both tables, so that it will present the info from the time monitor table along with the summary of the length field from every record that fits the date range in the appointment log table? It would be good if it could also include the count for the total number of appointments that day.

It would also be good to have the info either grouped by day / week / month / yr or to have a drop down option in the query to select the date and summarise the findings.

THANKS!! )

 

[Beetle]

Can you give us a representative example of your table structure and the type of data they contain, plus an example of the results you are expecting to see?

 

[supersezzie]

Hi,

thanks for your help. I've attached a picture of the two tables to give you an idea. I need to use all the fields from the Time Monitor table except the ID. I just need to use the date of contact and length of session from the Student Appt Log table, and possibly the count of the number of students seen that day.

I would like to compile the date from both into one field to sort by, so that I have something like:

Date (either drop down selection to sort by day/month/yr or in separate queries if necessary)

Total time in minutes spent in:
Student contact (from student appt log)
Drop-in
Telephone
Letters
All the other fields from the Time Monitor Table

Plus number of students seen (the count of records for that date)

An average per day/week might be good if the query is for a month or year but again only if this is not a major hassle!

Thank you so much for your help

 

[Beetle]

You can join the two tables in a query on the Date field and return whichever fields you need from both tables to display the details of all the records. As far as your summary of length and count of appointments needs, you may need to do that separately, either in another query, or in a text boxes on your form using DCount and/or DSum.

I am curious about something you said in your first post;

It contains a number of fields where I enter the number of minutes spent on each task at the end of the day.

Are these values that you're calculating manually (or otherwise deriving) from information that is not stored in this database (i.e. you don't track info on tasks or their start/stop times)?

BTW - The word Date is an Access/VBA Reserved Word, and should not be used as the name of a user defined object (like a table field, form control, etc.)

 

[supersezzie]

Hi, thanks for this. Yes, the data for the time values are just added on the form manually as the day goes on. They're not stored anywhere else or calculated from any more detailed info.

Thanks for your help - I will have a play with the query and date fields and see what I can come up with!