Union Query - Group By

[raffi]

Hello, im going to try and keep this as simple as i can k,

I have 2 tables, with the exact same fields, and the exact same records, but with one exception, one of the tables have some missing data in it, so i created a union query out of the 2 tables, and i added the Group By within Totals of the union query.

so my question is, is it possible to populate the blank missing data within the union query, with the second tables available Value ?

so in conclusion im trying to group 2 records, one of which has missing data, and other has the available data, into 1 record which has all the data,

any help would be much appreciated.

[ItsMe]

The UNION operator may give you the results you desire. Are you trying to update records in a table? If I was faced with a situation where I had two tables and I wanted to merge them into one, I would use the Unmatched Query Wizard to locate the records that NEED to be updated.

[June7]

UNION query is not appropriate tool for updating existing records in one table with values from another table. If they have the exact same records, why do you need to duplicate the data into both tables? The tables have the same record identifiers? Build query that joins on the common identifier.

[raffi]

i will look up the UNION operator, and unmatched query wizard, first thing tomorrow morning, cuz my brain processing power has gone dangerously low lol from all the thinking

here's a much more in dept detailed explanation of my issue


i have 2 conjunction tables, which have the exact same fields, and the exact same records which are, [ItemID] [ItemName] [LocationID] [LocationName] [QTY] [ExpiryDate] so i created a Union query out of the 2 conjunction tables, and added "Group By" to all fields, and "Sum" to "QTY" field


ok, within the first conjunction table im adding value to all fields, except the [LocationID] [LocationName] so, the [locationID] is 0 and [locationName] is blank.
and the next conjunction table, im adding the exact same Value of the first table but in addition im also adding value to the [locationID] and the [LocationName] which is automatic chosen, based on the [locationID]


and im trying to find a way to override the blank and the 0 fields of the first table with the seconds table locationName , inside the union query so 2 records will be 1

and again thank you for pointing me to the UNION operator and the unmactched query wizard i will look then up

[raffi]

oh hello june7 because its made out of 2 phases first receiving the shipment and then phase number 2 placing the items on the locations and after doing so,i gotta go back to the laptop and re-merge all the items i received with the locations alongside the expirydates, so the first table is receiving the shipment and the second table is putting items on shelves ( locations )

[ItsMe]

You may be able to address your issue through redesigning the tables an queries. If I have two separate sources for data and each source operates autonomously, I will merge them using VBA and DAO. I will employ the Unmatched Query Wizard to identify the Unmatched Records and use VBA to do the updates. It is how I do it and I am sure there are other ways...

To create an unmatched query, use the Wizard and select the "Find Unmatched Records" option.

[raffi]

theres something i forgot to mention, which u might be able to help me with, after i created the Union Query, i created another query "Out of the UNION Query" so, i have a normal query now, which the data is derived from the Union Query.

so now i can go into the normal design mode and manipulate the data in the familiar design mode which has criteria under each field, but i couldn't find a way to do what i have as an example below.

lets say table 1 has this one record in it

ItemName: Item 1 LocationName: Blank GFQTY: 5 ShelfQTY: 0

table 2 has this one record

ItemName: Item 1 LocationName: A1 GFQTY: -2 ShelfQTY: 2

trying to get query to look like

ItemName: Item 1 LocationName: A1 GFQTY: 3 ShelfQTY: 2

[ItsMe]

If one field from one table is missing data and a different field from the other table is missing data, a UNION may be the appropriate way. You will have to be careful associating records between the two tables. I am not seeing the big picture yet. I am having a hard time imagining a real world situation where the UNION would retrieve data from two sources to produce, what could be/represent, a table for production.

If the UNION is creating the results you need and the PK fields are agreeable, then I guess it works. You could use the results to append a permanent production table. I would study the UNION ALL operator to help you understand the difference between UNION and UNION ALL before you analyze the data within your results.

[raffi]

i just figured out i have a better way of explaining of my issue

lets just say i have a query with just 3 fields,

Item, Location, QTY

we add the group by laptop and location fields , and sum to the QTY field

ok now lets add 2 records into this Query

first record : laptop , blank , 1
2nd record : laptop , A1 , 1

now the 2 records wont group of course cuz the first record has a blank instead of A1
so what im asking, is there a way to add a formula in the criteria of the of the location where it would replace all the blanks with whatever location is available in the second record?

so the end result would be laptop, A1, 2

[raffi]

hey mates im so sorry for all the questions i really appreciate all the help i received and i will look up the append and delete query's and think through it ty all for help u guys are great for now i stopped the operation