Want to edit a table based on a query of a query of several querie

[Elizabeth]

First, I need to note that I don't know SQL, so assuming you manage to glean
what I need from my convoluted explanation below, please try and explain how
I'd do this without using SQL.

I have 9 Access tables of sales orders, built from 9 external reports
(spreadsheets that are imported weekly) that are mostly redundant in their
data. Almost every sales order shows up in 3 or more of these reports...each
having the same basic customer and order info, with a couple different
columns thrown in, just to make life interesting. I have to run a weekly
report that consolidates the data from each of these tables, but I need to
weed out the duplicates between tables.

As an example, let's say SO# 12345678 and all of it's basic data shows up in
4 of the 9 tables, with some extra columns thrown in that are unique to each
table. In order to get all of the data I need into one consolidated query, I
had to create a separate query for each individual table that contains a
limited number of columns..each of the 9 queries put the necessary columns in
the same order with the same labels. (SO#, Posted date, Salesrep, value,
endcustomer, shipCustomer, Service or product?, Operation, region and AM)
Then I run a big query of all 9 queries to consolidate the data into one big
report (using a "union all" join that I copied and pasted from one of the
discussions I found on this site).

However, my one big report now contains hundred of duplicates. And none of
the duplications happen within the same table.

Now try not to laugh too hard, but I created a "duplicates query" that mines
my big consolidated query which mines the 9 different table-based queries.
(I'm a solid BusObj user, so I understand the concepts behind how MS access
works, but am still a frustrated novice at turning the concepts into
deliverables.)

So because my "duplicates query" is a query of a query of 9 queries. I can't
delete the records straight from the "duplicates query" (thereby having them
also get deleted from their respective tables...which I what I want to have
happen) like I could if the query went directly into the tables.

My band-aid solution to this issue isn't working. I'm having to copy the
SO# from the "duplicates query", paste it into the search tool on the ribbon
of each of the the appropriate tables and then delete the record from each
table. I can't keep doing this for 500+ records each week. It's killing me.

I'd LIKE to create a search thingy (query, form, whatever, I don't care as
long as it works) for each table that will allow me to paste a list of 50+
sales order numbers at a time, hit search and have them highlighted so I can
then delete them.

I know that my explanation is probably completely confusing, so feel free to
request clarification if you're willing to take on the challenge of helping
me out with this. (Picture me prostrate on the floor, begging for assistance
and that would be fairly accurate, LOL.)

Thanks,
Elizabeth

 

[KARL DEWEY]

There is a way that is sooo much easier.
First create union query like this --
SELECT [SO#]
FROM Table1
UNION SELECT [SO#]
FROM Table2
UNION SELECT [SO#]
FROM Table3
.....
UNION SELECT [SO#]
FROM Table9

Do not use UNION ALL as you want the [SO#] only once.

Use that query in design view and add all of the other nine tables.
Click on the union query [SO#] field and drag to the first table [SO#]
field. Click and then double click the the connecting line. Select the
option to view all records from the union query and only those from first
table that match.
Do the same for the other 8 tables.
Click on the union query [SO#] field and drag down to the Field row of the
design view grid. Drag the other fields you need from the 9 tables.

This puts all the data in to a single query output without duplicates.

 

[Elizabeth]

OK, you are THE BEST!!!!!!!!!!!!!!!!!! It's wasn't exactly what I needed
(because I do need SOME SO# duplicates since there are orders that get their
bookings value credit "split" between 2 teams.) But I figured out how to get
around that, using your explanation, below. It was wonderfully thorough and
got me the desired end result.

Thank you, thank you, thank you!!!!!!

Have a GREAT week!!!

Very Sincerely,
Elizabeth

There is a way that is sooo much easier.
First create union query like this --
SELECT [SO#]
FROM Table1
UNION SELECT [SO#]
FROM Table2
UNION SELECT [SO#]
FROM Table3
....
UNION SELECT [SO#]
FROM Table9

Do not use UNION ALL as you want the [SO#] only once.

Use that query in design view and add all of the other nine tables.
Click on the union query [SO#] field and drag to the first table [SO#]
field. Click and then double click the the connecting line. Select the
option to view all records from the union query and only those from first
table that match.
Do the same for the other 8 tables.
Click on the union query [SO#] field and drag down to the Field row of the
design view grid. Drag the other fields you need from the 9 tables.

This puts all the data in to a single query output without duplicates.

--
Build a little, test a little.

First, I need to note that I don't know SQL, so assuming you manage to glean
what I need from my convoluted explanation below, please try and explain how
I'd do this without using SQL.

I have 9 Access tables of sales orders, built from 9 external reports
(spreadsheets that are imported weekly) that are mostly redundant in their
data. Almost every sales order shows up in 3 or more of these reports...each
having the same basic customer and order info, with a couple different
columns thrown in, just to make life interesting. I have to run a weekly
report that consolidates the data from each of these tables, but I need to
weed out the duplicates between tables.

As an example, let's say SO# 12345678 and all of it's basic data shows up in
4 of the 9 tables, with some extra columns thrown in that are unique to each
table. In order to get all of the data I need into one consolidated query, I
had to create a separate query for each individual table that contains a
limited number of columns..each of the 9 queries put the necessary columns in
the same order with the same labels. (SO#, Posted date, Salesrep, value,
endcustomer, shipCustomer, Service or product?, Operation, region and AM)
Then I run a big query of all 9 queries to consolidate the data into one big
report (using a "union all" join that I copied and pasted from one of the
discussions I found on this site).

However, my one big report now contains hundred of duplicates. And none of
the duplications happen within the same table.

Now try not to laugh too hard, but I created a "duplicates query" that mines
my big consolidated query which mines the 9 different table-based queries.
(I'm a solid BusObj user, so I understand the concepts behind how MS access
works, but am still a frustrated novice at turning the concepts into
deliverables.)

So because my "duplicates query" is a query of a query of 9 queries. I can't
delete the records straight from the "duplicates query" (thereby having them
also get deleted from their respective tables...which I what I want to have
happen) like I could if the query went directly into the tables.

My band-aid solution to this issue isn't working. I'm having to copy the
SO# from the "duplicates query", paste it into the search tool on the ribbon
of each of the the appropriate tables and then delete the record from each
table. I can't keep doing this for 500+ records each week. It's killing me.

I'd LIKE to create a search thingy (query, form, whatever, I don't care as
long as it works) for each table that will allow me to paste a list of 50+
sales order numbers at a time, hit search and have them highlighted so I can
then delete them.

I know that my explanation is probably completely confusing, so feel free to
request clarification if you're willing to take on the challenge of helping
me out with this. (Picture me prostrate on the floor, begging for assistance
and that would be fairly accurate, LOL.)

Thanks,
Elizabeth