Use the results of a query in another query

[lamore48]

Hi All,
I have a database with a couple of tables in it. One table is an input table and the second table is a detail table. When a record is entered it has a tool box which is unique. Each number is in sequence and has the same main number with a sequential trail number(i.e. 46001-1, 46001-2, 46001-3, 46001-4). So, in that series there are 4 inputs into the database. I want to count them together, and see how many instances there are of that particular tool box. So I wrote the following query:

SELECT Left([CGBoxStatusIn]![CGBox],5) AS CGBox_Number, Count(Left([CGBoxStatusIn]![CGBox],5)) AS HowmanyFROM CGBoxStatusIn INNER JOIN ToolDetail ON CGBoxStatusIn.CGBox = ToolDetail.[CGBox]
GROUP BY Left([CGBoxStatusIn]![CGBox],5);

And this gives me the result I'm looking for, now I am trying to use the results from this query and I'll give a example of the data :

CGBox ScanIn id missing ScanOut missingOut 46001-3 2/18/2018 7:14:08 AM 416 0 0 46002-1 2/13/2018 7:14:16 AM 417 5 0 46001-1 2/10/2018 8:48:49 PM 418 6 0 46001-2 2/7/2018 8:49:01 AM 419 4 0 46001-4 1/21/2018 8:49:17 AM 420 0 0 46002-2 1/18/2018 8:49:25 AM 421 3 0 46002-3 2/3/2018 8:49:35 AM 422 0 0

So, my other table has tool detail in it, which shows how many tools are in a box, so I need to inner JOIN on the other table and get the get the amount of tools in a box, so I can multiply that by how many tools are in the total minus the missing which is in another field in my original table. So I would link the tables on the CG Box number count the number of instances of 46001 (in this case there are 4) and inner join and find out in the tool detail table that there is 8 tools per box * 4 boxes in this example. For the other number in this table 46002, there are 3 boxes in the series and 10 tools per box.
I hope I'm not over explaining here but, in a nutshell I need the above query to inner join to another table so I can grab the data in the field which says how many are in a box and multiply it by how many boxes are in that series...so for the 46001 example the anwser would be 4 boxes times 8 tools in a box


Thanks,
Lenny

[isladogs]

Not sure why this hasn't already been answered.

I think you have over explained what you want.
Have you tried joining your query to a table in another query? It's a perfectly normal thing to do.
If you have tried it, what happened?
If you haven't, then please do so

[lamore48]

Not sure why this hasn't already been answered.

I think you have over explained what you want.
Have you tried joining your query to a table in another query? It's a perfectly normal thing to do.
If you have tried it, what happened?
If you haven't, then please do so

Don't know how, that's was why I posted

[isladogs]

Can you upload the db with those tables & query. If so, i'll look at it tomorrow

[lamore48]

I can tomorrow.... Thanks

[kd2017]

I need the above query to inner join to another table so I can grab the data in the field which says how many are in a box and multiply it by how many boxes are in that series

You can simply use a saved query as a source of a query exactly the same way you use a table.
Query examples:
SELECT * FROM table_name
SELECT * FROM query_name
SELECT * FROM table_name
SELECT * FROM table_name INNER JOIN query_name
SELECT * FROM query_name INNER JOIN table_name
SELECT * FROM query1_name INNER JOIN query2_name
etc...

[lamore48]

Hi,
I am uploading my database that has the query that I'm trying to figure out how to add it to a second query and get a total result. So the query named "qry_Cnt_Number_of_Instances" is grabbing the first 5 numbers of a tool set number and adding them together(that part works). Next I want to get "How Many" "CGBox_Number" * tooldetail.Number_Of_Boxes *tooldetail.toolsperbox. So for the first Box Number "46001" it would be 4 boxes * 8 tools per box =32 total tools.


Thanks for your help,
Lenny

[isladogs]

A lot of comments on your database - not good I'm afraid
You may regret me belatedly picking up your post!

1. You have 7 'class modules' Class 1 to 7 - I'm not clear why they are there or why you think these are all class modules

2. I strongly recommend you avoid underscores in database object names e.g. replace qry_Tools_In_Process with qryToolsInProcess

3. NEVER use the same name for different objects. You have a form and a query called qry_Tools_In_Process
Perhaps your form should be frmToolsInProcess

4. Prefix all reports with rpt not qry!

5. Your field naming breaks every rule under the sun
Do NOT use spaces or special characters such as # or " or '. Keep names short and clear.
For example the field:
# of Days of Prod'n Tool Change Will Last => ToolChangeDays or similar
"Actual" GTSV Tool Life => GTSVLife or similar
I've made a stramlined copy of this table called ToolDetailNEW

6. Each table MUST have a primary key field so each record is uniquely identified
Without a primary key field, many queries will be read only
Add a suitable field to ToolDetail e.g. ToolID or use CGBox as the PK field as it seems to be unique
For now, I've assigned cGBox as the PK field
Similarly the table CGBoxStatusIn could use CGBox as the PK field - if you do so, the id field is probably redundant and can be deleted

7. Data should only be stored once.
The 3 tables started CGBoxStatus have a lot of duplication and could probably be saved as one table
I've combined CGBoxStatusIn & CGBoxStatusOut as one table CGStatus
You could also merge the records in the Archive table - I'll leave that to you!

8. Calculated values should normally be calculated on demand in queries & NOT stored in tables
I've done a simplified query qryCountCGBoxInstances which includes the calculated HowMany field
So this field shouildn't also be saved in the ToolDetail table as 'Number of Boxes'

9. Will there ALWAYS be 8 tools in each box? If so, this could be saved as a constant - done in modConstants (but not used)

10. Code modules should ALWAYS have Option Explicit as the line after Option Compare Database
This prevents undefined variable errors. I've added it wherever missing
You have correctly ticked Require Variable Declaration in VBE options so all new modules will have this.
However there are code errors in modules where it was missing
e.g. Class7 errors on Dim rst_CGBox As adodb.Recordset

To repeat an earlier comment - don't use underscores in variable names
I'd also suggest using DAO.Recordset rather than ADODB.Recordset - more powerful & easier to use

Sorry to be so critical but unless you sort out all these issues now, it will cause you MAJOR grief later on.
I recommend you spend time learning the basics of database design before proceeding
For example watch the Access vidoes on You Tube by Steve Bishop or Richard Rost
Choose whichever you prefer - both are very good

And at the end of all that, you asked about a query.
You'll be glad to know its simple!!!! See qryCountTotalTools

Modified database attached - you need to go through and check each database object and all code

Good luck from here on

[kd2017]

...

2. I strongly recommend you avoid underscores in database object names e.g. replace qry_Tools_In_Process with qryToolsInProcess

...
To repeat an earlier comment - don't use underscores in variable names

...

Not to hijack the thread but can you elaborate on why no underscores? This is the first I've heard this.

[isladogs]

Underscores are valid but add unnecessary typing.
Also easy to make mistakes in placing of underscore especially where more than one is used

[lamore48]

I appreciate your help on this....first off, I am a novice at this, kind of getting by with Youtube videos and help here on the forum. So the class objects came about from when I imported code it just created these class objects, I then copied and modified as I needed to. So, I looked at what you fixed and I have a couple of questions. I have this setup to read barcodes which are on the tool boxes(i.e. 46001-1, 46001-2, 46001-3, 46001-4). I then grab that number and insert into CGBoxStatusIn table. When you altered it you split the box number off into 2 fields named "CGBox" and "BoxNumber", which is good. One question I have is if they are both primary keys they both will have more than one entry of a given number(in other words...duplicates). So in this example there are 4 entries for the number "46001", the next 3 records "46002" is listed 3 times because there are 3 boxes of that tool. Also, "Question #9. Will there ALWAYS be 8 tools in each box? If so, this could be saved as a constant - done in modConstants (but not used)". The anwser is No, there are different tool amounts in different boxes, You have to know that I only had a couple of tool numbers entered into the database so far, and there are probably 300-400 different tool numbers that are going to eventually be entered into the database and they have different amounts of tools in each box(depending on size of tool). Some have 8 tools, some 5, some 10. That's why I wanted to INNER JOIN on the tooldetail table so I could look it up based on the CGBox number.

Again thanks for your help,
Lenny

[isladogs]

I appreciate your help on this....first off, I am a novice at this, kind of getting by with Youtube videos and help here on the forum. So the class objects came about from when I imported code it just created these class objects, I then copied and modified as I needed to. So, I looked at what you fixed and I have a couple of questions. I have this setup to read barcodes which are on the tool boxes(i.e. 46001-1, 46001-2, 46001-3, 46001-4). I then grab that number and insert into CGBoxStatusIn table. When you altered it you split the box number off into 2 fields named "CGBox" and "BoxNumber", which is good. One question I have is if they are both primary keys they both will have more than one entry of a given number(in other words...duplicates). So in this example there are 4 entries for the number "46001", the next 3 records "46002" is listed 3 times because there are 3 boxes of that tool. Also, "Question #9. Will there ALWAYS be 8 tools in each box? If so, this could be saved as a constant - done in modConstants (but not used)". The anwser is No, there are different tool amounts in different boxes, You have to know that I only had a couple of tool numbers entered into the database so far, and there are probably 300-400 different tool numbers that are going to eventually be entered into the database and they have different amounts of tools in each box(depending on size of tool). Some have 8 tools, some 5, some 10. That's why I wanted to INNER JOIN on the tooldetail table so I could look it up based on the CGBox number.

Again thanks for your help,
Lenny

Don't worry - we all were novices once & my early efforts were appalling - yours isn't that bad!
Odd that the class modules were created automatically - anyway you are using almost none of that code so most could be deleted

Similarly delete my modConstants - I didn't use it as I thought they wouldn't all be 8

I wasn't aware you were scanning in barcodes.

I've now modified the database as follows:
Table CGBoxStatus now has a field CGBoxScanCode (text) which would be used for your values like 46001-1 as before
The two fields CGBox & BoxNumber have been deleted again as these are derived using the query qryCountCGBoxInstances

The two queries qryCountCGBoxInstances & qryCountTotalTools have all you need for this task
Similarly the only tables needed are CGBoxStatus & ToolDetailNEW.
That is unless you also need to include the data in the table CGBoxStatusIn_Archive

Hope that helps
Good luck with your project

[lamore48]

The reason I have the archive table is when a box is scanned out(processed) I append the archive table and delete the record from the CGBoxStatus table so later I can do a history report on how many times the box came in and out in a given time period.


Again, I appreciate your help,
Thanks,
Lenny

P.S.
I was just looking the queries and was wondering how to subtract the missing tools from the count. If I go to the qryCountCGBoxInstaces and add missingin to the query it doesn't work on the qryCountTotalTools if i change the query to say "TotalTools: [NumberCGBoxes]*[ToolsPerBox]-[MissingIn]" logically it looks like it should calculate it but what it does is now list in rows instead of counting the "46001" as 4 instances * 8 tools -5 missing ins ........

[isladogs]

I see the point but would suggest you keep all data in one table.
Years ago, in a similar situation I chose separate tables for archived data.
It caused a huge amount of work to maintain and eventually I scrapped that and merged the data again.

Instead, just use an extra yes/no field called Active to indicate which records are current and which are archived.
Also revert to an autonumber ID field as you will obviously repeat the box details in more than one record

Queries will be able to distinguish current and archived data using the Active field

[lamore48]

Hi, was just wondering if you saw my P.S. from my last post? " how to subtract the missing tools from the count. If I go to the qryCountCGBoxInstaces and add missingin to the query it doesn't work on the qryCountTotalTools if i change the query to say "TotalTools: [NumberCGBoxes]*[ToolsPerBox]-[MissingIn]" logically it looks like it should calculate it but what it does is now list in rows instead of counting the "46001" as 4 instances * 8 tools -5 missing ins ........



Is this possible?


Thanks,
Lenny