Information from multiple query's on a report

[pleshrl]

I am not nearly as good at this as I think I am, so if my question does not make sense, it is most likely the question.



It starts with two tables. The first has records of new projects and includes things unique to that project - start date, equipment used... The second table is linked to the first and has information on each trial used to try and complete the project, things like on Tuesday we used tool A and it did not work, on Wednesday we used tool B and it did not work... Just dates and tooling numbers.

I have a query that shows each record from the first table and another that pulls information from a specific record.

What I want is to include a count of how many sub records (?) are related to each main record and information on a specific trial.

Something like:

Project number number of tries next try schedule
3322 1 Monday
1234 4 Tuesday
5555 2 Wednesday

What I always get is this:
Project number number of tries next try schedule
3322 1 Monday
1234 1 last week
1234 1 some other day last week
1234 1 you guessed, information on a previous trial
1234 1 Tuesday
5555 1 old trial date
5555 1 Wednesday

What am I missing? I thought a sub-report, but could not make it work.

I cannot build a query that has a count and information from a specific sub-record either, so I made two.

[June7]

You want to count all records but only see the information from the latest? Does the detail table have a unique ID field (such as an autonumber)? Assuming data is entered chronologically, use the ID to order records and retrieve the TOP 1 for each project number. Review http://allenbrowne.com/subquery-01.html especially the example for Top N records for each group.

Is project number the PK/FK for linking tables?

You could build a report with Grouping & Sorting and aggregate calcs in group footer. This will allow display of all detail info and calculate summary data. The RecordSource would be the detail table, include the main table if you need other info than just the project number.

Otherwise, options:

1. DSum domain aggregate function to calculate the total then also apply a filter for the desired records

2. create an aggregate (Totals) query and join it to the source table and apply a filter for the desired records

[pleshrl]

Ummmm HUH?
That link was too much for me. I need to step back.

Let me see if I can answer your questions, cause you know a lot more than me.
I do want to count all records in the sub-record (is that even correct?) and I want to see data on a specific record, which should be the most recent.
The detail table does have a unique field ID, an autonumber
If I build a report with grouping and sorting, wont that show me all the sub-records? I only want to see one.
Whats a Dsum and how do I use it?

Actually, using the 'LAST' worked for the query, and therefore the report. Using last might make it tough to put old data in (an old trial will get a higher autonumber), but if I draw the line in the sand now, everything from now on will work.

Thanks for the suggestions, I am on my way and will be back with more questions I am sure!

[June7]

Yes, the report would show all detail records because the count is done on the report

Sorry I should have said DCount instead of DSum. These are domain aggregate functions (DLookup, DAvg, DCount, etc). Access Help has guidelines.

Glad you have something working.