Combining two queries without a union query

[shabbaranks]

Hi guys,

Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.

Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.

If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.

Any help is appreciated - thank you

 

[plog]

What do you mean by the fields aren't exactly the same? Are you talking the name or the number and/or types of fields? You can always rename field names so that they match and then UNION them.

However, you might just want to examine the underlying queries and see if you can merge the logic. Can you post the SQL of both those queries?

 

[shabbaranks]

Thanks for the quick reply, if I change the names to match and create a union query I get duplicate results. I think this is because in one table the material description is different to the material description of the other table so Access thinks that its two separate results. Here's my union query SQL

SELECT dbo_PO_Header.PO, ([Name]) AS [Company Name], dbo_PO_Header.Order_Date, dbo_PO_Header.Issued_By, dbo_User_Values.Text1 AS [Cost Code], dbo_Source.Material, dbo_Source.Description, dbo_PO_Detail.Order_Quantity, dbo_PO_Detail.Unit_Cost, dbo_PO_Detail.[Order_Quantity]*[Unit_Cost] AS [Total Cost], dbo_PO_Detail.PO_Detail
FROM dbo_Vendor INNER JOIN (dbo_Source INNER JOIN (dbo_User_Values INNER JOIN (dbo_PO_Header INNER JOIN dbo_PO_Detail ON dbo_PO_Header.PO = dbo_PO_Detail.PO) ON dbo_User_Values.User_Values = dbo_PO_Header.User_Values) ON dbo_Source.PO_Detail = dbo_PO_Detail.PO_Detail) ON dbo_Vendor.Vendor = dbo_PO_Header.Vendor
WHERE (((dbo_User_Values.Text1) Like "PR0*"))
UNION
SELECT dbo_PO_Detail.PO, dbo_Vendor.[Name] AS [Company Name], dbo_PO_Header.Order_Date, dbo_PO_Header.Issued_By, dbo_Job.[Job] AS [Cost Code], dbo_Material_Req.Material, dbo_Material_Req.Description, dbo_PO_Detail.Order_Quantity, dbo_PO_Detail.Unit_Cost, dbo_PO_Detail.[Order_Quantity]*[Unit_Cost] AS [Total Cost], dbo_PO_Detail.PO_Detail
FROM (dbo_PO_Header INNER JOIN (dbo_PO_Detail INNER JOIN (dbo_Source INNER JOIN (dbo_Job INNER JOIN dbo_Material_Req ON dbo_Job.Job = dbo_Material_Req.Job) ON dbo_Source.Material_Req = dbo_Material_Req.Material_Req) ON dbo_PO_Detail.PO_Detail = dbo_Source.PO_Detail) ON dbo_PO_Header.PO = dbo_PO_Detail.PO) LEFT JOIN dbo_Vendor ON dbo_PO_Header.Vendor = dbo_Vendor.Vendor
WHERE (((dbo_Job.[Job]) Like "PR0*"));

 

[plog]

You don't get duplicate results, each record is unique and it sounds like you have identified why. So which description is correct and should show up in the final result?

 

[shabbaranks]

Im not sure each record is unique though - if you look at the image PO 4120 and 4109 and 3872 are all duplicates (all be it from separate query results)

 

[plog]

Nope, they look unique to me. Look at the Material field, each PO/Material permutation is distinct.

 

[shabbaranks]

SO the fact ones got data in it and the other hasn't means that it'll treat them as "separate" records?

I appreciate its difficult to assist without seeing the DB - surely there must be a way to pull records without duplication as they are all linked with PO detail?

Theres a pint in it if you can help me get this to work

 

[shabbaranks]

You don't get duplicate results, each record is unique and it sounds like you have identified why. So which description is correct and should show up in the final result?

In answer to this - it doesn't really matter as they are both valid results, its just the duplication which I would like to get rid of.

 

[plog]

Again, they are not duplicates, each row of data is distinct. What would you like as the results?

 

[Galaxiom]

You can always rename field names so that they match and then UNION them.

Renaming the fields to match would be quite unecessary. The field names of the first section of a union query define the output fieldnames regardless of the names used in the subsequent sections.

 

[shabbaranks]

Hi,

Ive almost got it working with a union query. What I did was remove the PO_Detail so any duplicates that weren't "actually" because they have different PO_Detail numbers are now just one entry.

What I need to get around now is that I need to remove empty Material results as these are the duplicates. I know I can just filter on >1 and that gets rid of them but ideally I would like to make it more streamlined than that.

Thanks again