Query based on two other querys.

[Pintglass]

Hello
I have created a quotation database in this I have created two querys with calculated fields. One query for materials and one for labour I'm now trying to bring these querys together to create a report.

The problem I'm having is that when I create the main query for every item that appears in the materials query I'm getting the same amount of labour items showing, even though I may only have one labour item compared to many material items.

Any help with this would be much appreciated
Regards Pintglass

 

[NickHa]

You need to look at the way your two queries are joined. Have a look at INNER JOIN to determine the linkage if you are working directly in SQL. Otherwise, you can use the query wizard to construct a query which selects the relevant records from each data source.

 

[Pintglass]

Thanks for the reply.
I have tried different joins but it doesn't seem to make any difference.

The main problem I have is when I try to sum the calculated fields in the report.
For example if I have 5 items in the materials query and only 1 item in the labour query the labour query sums 5 items as well.

Thanks Pintglass

 

[gblack]

If you are joining two queries together with a shared ID field...

If you have five of one item in a column, from query #1... and that query is joined to another query (i.e., Query #2) by the same ID field... the if the item is not in the same named column in query #2, it will show up 5 times.

If labour and materials are in the same field/column Use a Union query to stack the data. Unfotunately with UNION queries you have to write the SQL.

Maybe tell us a bit more about your query structure and then show us what you want the output to look like.

GL,
Gary

 

[Pintglass]

Thanks gblack for the reply.

On my main form I have 2 sub forms one for materials one for labour, on this main form I have 3 buttons to create reports with, one report containing both labour and material details and total, one report with material total and labour total and one report with just a total. I was trying to create one main query to do all of this but I've now used separate querys.
The 2 querys that I created are to just to do the calculations .
Query 1
Quantity * unitPrice for materials

Query 2
Quantity * day rate for labour

Regards Pintglass

 

[gblack]

Thanks gblack for the reply.

On my main form I have 2 sub forms one for materials one for labour, on this main form I have 3 buttons to create reports with, one report containing both labour and material details and total, one report with material total and labour total and one report with just a total. I was trying to create one main query to do all of this but I've now used separate querys.
The 2 querys that I created are to just to do the calculations .
Query 1
Quantity * unitPrice for materials

Query 2
Quantity * day rate for labour

Regards Pintglass

Yeah I am not sure I understand the issue still... because I don't know how your two queries are structured. I am assuming Quantity isn't the same for each query...? Like Quantity of Items vs. an Hour Quantity for labour... or something like that. So... if you really need them wrapped up in one query, and the field names are the same... or you could change both query fields to a name like [Rates] then Union the two together (as shown below) adding another field, calling it something like [Rate_Type]... where Rate_Type for query 1 would be set to "Materials" and Rate_Type for query 2 would be "Labour"


Basically something like:

SELECT Rates, "Materials" as Rate_Type
FROM [Query 1]
UNION SELECT Rates, "Labour" as Rate_Type
FROM [Query 2];

 

[Pintglass]

Hi gblack

It would probably be easier for me to send you my db file so you can see what I'm doing wrong.

Let me know if thats OK

Thanks pintglass