Combine these two Queries?

[Jason Gyetko]

Is there any way to combine these two queries into one? I have tables
Item_Master & Kit_Master which are the source tables. Query 2 is using
both Item_Master (table) & qryKit1 (query) & Kit_Master (table) as its
record source because the Kit_Master table has 2 fields in it that get
joined back to the same field in Item_Master.

Item_Master key field is SKU
Kit_Master key fields are KitNo & ComponentNo

Item_Master - SKU contains a record for each Kit_Master - KitNo and
also for each Kit_Master - ComponentNo

qryKit1:
-----------
SELECT Item_Master.SKU, Item_Master.OnHand
FROM Item_Master

qryKit2:
-----------
SELECT Kit_Master.KitNo, Min(Item_Master.OnHand/[Qty]) AS Availability
FROM (Kit_Master INNER JOIN Item_Master ON Kit_Master.ComponentNo =
Item_Master.SKU) INNER JOIN qryKit1 ON Kit_Master.KitNo = qryKit1.SKU
GROUP BY Kit_Master.KitNo;

I can use two queries if I need to, I was just wondering if it could be
done. Thanks.

[Rich P]

I'm sure it is possible to combine your queries, but in order to make
the results come out as you desire you should post 3 samples of your
data -

1st - a few rows from the primary table

2nd - a few of the corresponding rows from the 2nd table

3rd - the rows from the desired resultset

So run your queries from your source tables and then show a few rows
from the resulting query. Then it will be easier to modify your queries
into 1 query.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Jason Gyetko]

Thanks for the response, here's the info you requested.

Item_Master (qryKit1 is the same)
---------------------------------------
SKU OnHand
55-1513-01 6600
69-0044-01 0
71-0831-01 51
84-0072-01 0
84-0113-01 59
501-00007-02 70
502-00008-01 438
55-0722-01 0
55-0840-01 65
55-0841-01 150
55-0941-01 224
55-1254-02 231
55-1255-01 199
55-1256-01 0
55-1257-02 80
55-1258-01 70
55-1259-01 32
55-1469-02 164
55-1513-01A 3550
55-1513-01B 452
55-1513-01C 2200
T NUTS 0
Kit_Master
---------------------------------------------------------------------
KitNo ComponentNo Qty
55-1513-01 55-1513-01A 1
55-1513-01 55-1513-01B 1
55-1513-01 55-1513-01C 1
69-0044-01 502-00008-01 1
69-0044-01 55-1254-02 1
69-0044-01 55-1256-01 1
69-0044-01 55-1258-01 1
69-0044-01 55-1259-01 1
71-0831-01 501-00007-02 1
71-0831-01 502-00001 1
71-0831-01 55-1469-02 1
84-0072-01 55-0722-01 1
84-0072-01 55-0840-01 2
84-0072-01 55-0841-01 4
84-0072-01 55-0941-01 1
84-0072-01 55-1254-01 1
84-0072-01 55-1255-01 1
84-0072-01 55-1256-01 1
84-0072-01 55-1257-02 1
84-0072-01 55-1258-01 1
84-0072-01 55-1259-01 1
84-0072-01 T NUTS 4
84-0113-01 55-0584-01 1
84-0113-01 55-1154-01 1
84-0113-01 55-1155-01 1
qryKit1:
---------------------------------------
SKU OnHand
55-1513-01 6600
69-0044-01 0
71-0831-01 51
84-0072-01 0
84-0113-01 59
qryKit2: (result I'm looking for - Availabilty)
---------------------------------------
KitNo Availability
55-1513-01 452
69-0044-01 0
71-0831-01 70
84-0072-01 0
84-0113-01 0
Rich P wrote:

I'm sure it is possible to combine your queries, but in order to make
the results come out as you desire you should post 3 samples of your
data -

1st - a few rows from the primary table

2nd - a few of the corresponding rows from the 2nd table

3rd - the rows from the desired resultset

So run your queries from your source tables and then show a few rows
from the resulting query. Then it will be easier to modify your queries
into 1 query.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Thanks. I was able to reproduce your queries with your data (thanks for
providing the original sql code, also) and I was able to get the same
query results as you using your query sql. Then I was able to modify
your query as follows.

This query should do the trick for you. Just paste the sql into a query
window.

SELECT t1.KitNo, Min(t2.OnHand/[Qty]) AS Availability
FROM (Kit_Master t1 INNER JOIN Item_Master t2 ON t1.ComponentNo =
t2.SKU) INNER JOIN (SELECT Item_Master.SKU, Item_Master.OnHand
FROM Item_Master) t3 ON t1.KitNo = t3.SKU
GROUP BY t1.KitNo;

BTW, if someone ever asks for data, it is always best to provide it in
DDL format

--------------------------------------
Create Table Item_Master(SKU Text(50), OnHand Integer)
Insert Into Item_Master
Select '55-1513-01', 6600 Union
Select '69-0044-01, 0 Union
...
----------------------------------------

I had to parse out all of your data by hand. The above code would be
run in VBA using DoCmd.RunSql. But hey, just glad to help.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Jason Gyetko]

Thanks, that's exactly what I was looking for!! And now that I see how
it's done, I'll be able to use it in future queries. Thanks again for
taking the time to help.

Rich P wrote:

Thanks. I was able to reproduce your queries with your data (thanks for
providing the original sql code, also) and I was able to get the same
query results as you using your query sql. Then I was able to modify
your query as follows.

This query should do the trick for you. Just paste the sql into a query
window.

SELECT t1.KitNo, Min(t2.OnHand/[Qty]) AS Availability
FROM (Kit_Master t1 INNER JOIN Item_Master t2 ON t1.ComponentNo =
t2.SKU) INNER JOIN (SELECT Item_Master.SKU, Item_Master.OnHand
FROM Item_Master) t3 ON t1.KitNo = t3.SKU
GROUP BY t1.KitNo;

BTW, if someone ever asks for data, it is always best to provide it in
DDL format

--------------------------------------
Create Table Item_Master(SKU Text(50), OnHand Integer)
Insert Into Item_Master
Select '55-1513-01', 6600 Union
Select '69-0044-01, 0 Union
..
----------------------------------------

I had to parse out all of your data by hand. The above code would be
run in VBA using DoCmd.RunSql. But hey, just glad to help.

Rich

*** Sent via Developersdex http://www.developersdex.com ***