Use the results of a query in another query

[isladogs]

No I hadn't seen it. Did you add that later?
Happy to have a look. Are you using my new tables now?

[lamore48]

Yes I am.....

[isladogs]

OK - all done ... but first an apology
When I read you were using my tables, I decided (without thinking) to delete the old ones - except the Archive table
I then realised that all the forms/queries/reports needed modifying to work with the new tables. Doh!!!!
So I've rewritten them and whilst I was at it, removed all underscores, updated field names etc

AFAIK its all now working again but please check for yourself
The Tool Count report (rptToolSubCount) based on query qryToolSubCount has the same results as in your original version
BUT the results don't make any sense to me




Surely the report should show the number of tools per box - number missing e.g. 46001-1 should be 8-6=2
Anyway I've left it as it was for you to decide

I also haven't touched the code in the modules, most of which you can delete as its not in use
Then you should check it compiles with no errors - at the momoent it doesn't
Hopefully you can continue from here by yourself

Good luck

[lamore48]

Ok, so what I needed it to do, which is why the query groups all the 46001's together minus the missing tools

[isladogs]

But the query & report doesn't group them all together - it would make more sense if it did

[lamore48]

So, the query does combine them and totals them when you link it to the tool detail table it solid then up like you see in the report..... In other words I need it to count the 46001-1,46001-2, etc into there are a total of 4 boxes times 8 tools in a box minus the combined number of missing tools

[isladogs]

We seem to be going around in circles - I re-created what you originally had - is that what you want? (Option A)

Or each box separately (as now) with e.g. 46001-1 having 8-6 = 2 (Option B)

Or (Option C) should your report have one entry for all the 46001 boxes with 8*4 = 32 - 10 missing = 22
Similarly one entry for all 46002 boxes with 8*3 =24 - 8 missing = 16

Personally I think B or C but not A!

[lamore48]

Option C, one record

[isladogs]

Good. That's the correct answer! Attached

[lamore48]

Thank you, I really appreciate your help

[isladogs]

Glad we got there in the end!