Group by then Unmatch record

[mks123]

Hi All,
I’m trying to build ONE query to get unmatch record in two tables (table A and table B).

Both table have same structure just three filed ( SaleID, Product and Profit)
SalesID – Duplicate
Product – duplicate
Profit - Currency



Here what query Should Do:
Group three fields for both table (SaleID, Product and Profit (SUM)
And give record which doesn’t match for both tables.

[Bob Fitz]

Why do you have two separate tables with the same structure

[mks123]

there is situation, I have to compare two database almost have same entry but both from different system.

[isladogs]

As Bob has indicated, you shouldn't have 2 tables with the same structure.

I doubt it can be done in one query.
There are perhaps 5 parts to this
1. Aggregate query for table A: qryAggA
2. Similarly for B:qryAggB
3. Unmatched query for those in qryAggA but not in qryAggB
4. Similarly for those in qryAggB but not in qryAggA
5 A union query to combine the results of items 3 & 4

Each of these is easy to do but it may be that you don't actually want all of the above
For example, you MAY have been transferring data from table A to table B and want to know what's missing????
If so, omit steps 3 and 5

[mks123]

As Bob has indicated, you shouldn't have 2 tables with the same structure.-----> I haven't created this, every month I get excel with data which I need to compare.

I have just choice either I do in Excel or in Ms Access,so I import both in Ms Access and do comparison.

I was wondering there can't be ONE for for this particular task, btw I can do this divided query.

Thanks for reply.

[isladogs]

As Bob has indicated, you shouldn't have 2 tables with the same structure.-----> I haven't created this, every month I get excel with data which I need to compare.

I have just choice either I do in Excel or in Ms Access,so I import both in Ms Access and do comparison.

I was wondering there can't be ONE for for this particular task, btw I can do this divided query.

Thanks for reply.

As already stated, I believe you need several queries or SQL statements to do this.
A procedure can easily be written to run each in turn & then display the results
This could be run using a button click on a form.

You seem to be indicating you could do all the above yourself which is absolutely fine by me

I'm out all day but if you do need help, I could assist you late this evening (UK time) or tomorrow ...unless someone else does so first.
BUT first a few more things:

1. Do you intend to do anything more than display the output in Access? If not, you may as well just do this in Excel

2. What about the results that are in both tables? Are you just discarding them?

3. If I am going to provide a solution, please tell me
a) the actual names of the 2 tables
b) the datatype for the SalesID & Product fields
c) whether the 2 tables just get overwritten each month or added to. If the latter then you need a date field as well
d) whether you want the results for steps 3 & 4 displayed separately or combined with the union query

[mks123]

As already stated, I believe you need several queries or SQL statements to do this.
A procedure can easily be written to run each in turn & then display the results
This could be run using a button click on a form.

You seem to be indicating you could do all the above yourself which is absolutely fine by me

I'm out all day but if you do need help, I could assist you late this evening (UK time) or tomorrow ...unless someone else does so first.
BUT first a few more things:

1. Do you intend to do anything more than display the output in Access? If not, you may as well just do this in Excel

2. What about the results that are in both tables? Are you just discarding them?

3. If I am going to provide a solution, please tell me
a) the actual names of the 2 tables
b) the datatype for the SalesID & Product fields
c) whether the 2 tables just get overwritten each month or added to. If the latter then you need a date field as well
d) whether you want the results for steps 3 & 4 displayed separately or combined with the union query

Thank you so much!!!... for sure if I get any trouble while building query I'll get back.

Thanks for your Time.


Regards,
Mukesh Y