Compare 2 Tables

[Keith]

I have a database which has a list of products and prices in one table. I
have another table which is updated each day from a file of new sales. If a
new product is sold which is not in my pricing table then I don't report
correctly. What I need is a query which will tell me which items are in the
MASTER table but are NOT in the PRICING table. I don't need a full list of
contents, just a short list of NEW items.

I know I could do an append to the pricing table of the new items in the
master table, but I want to know which the items are first.

Any help appreciated?

Thanks

 

[Al Camp]

Keith,
The soultion to this problem is to not allow sales of products that don't
exist in your Products/Prices table! Bad data in... bad data out... You'll
be fighting this problem forever.

However, your query should relate Products to SoldToday via a key field
like a PartNo. But, try working it the other way around.
Use "Show all records in SoldToday and only those in Products where
PartNo matches" when linking the tables in your query. On the Products
PartNo field, a criteria of Null should show the SoldToday PartNos with no
Product match. Didn't test... but it should work. (And... there may be
more than one way to do this)
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[John]

Why do you have two tables. It's not good practice to have duplicate tables
with the same fields.

JOhn

 

[Vincent Johns]

Why do you have two tables. It's not good practice to have duplicate tables
with the same fields.

JOhn

Apparently, the OP has two similar tables because their contents come
from different sources: one [MASTER] table with catalog information,
that he maintains; and another Table with daily transactions, that
somebody else maintains, which the OP must reconcile with [MASTER]. And
my guess is that the records aren't totally identical; chances are that
[MASTER] contains some additional information that the daily sales
records do not contain.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John]

I thought a primary rule of database design is non duplication of data.
Shouldn't he have either a replication database or a network connection to a
single master table.

John

Why do you have two tables. It's not good practice to have duplicate
tables with the same fields.

JOhn

Apparently, the OP has two similar tables because their contents come from
different sources: one [MASTER] table with catalog information, that he
maintains; and another Table with daily transactions, that somebody else
maintains, which the OP must reconcile with [MASTER]. And my guess is
that the records aren't totally identical; chances are that [MASTER]
contains some additional information that the daily sales records do not
contain.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Keith]

Yes, as new products are launched, I often do not get informed until after
the event !! I therefore have more products than Prices !

I thought a primary rule of database design is non duplication of data.
Shouldn't he have either a replication database or a network connection to
a single master table.

John

Why do you have two tables. It's not good practice to have duplicate
tables with the same fields.

JOhn

Apparently, the OP has two similar tables because their contents come
from different sources: one [MASTER] table with catalog information, that
he maintains; and another Table with daily transactions, that somebody
else maintains, which the OP must reconcile with [MASTER]. And my guess
is that the records aren't totally identical; chances are that [MASTER]
contains some additional information that the daily sales records do not
contain.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I have a database which has a list of products and prices in one table.
I have another table which is updated each day from a file of new sales.
If a new product is sold which is not in my pricing table then I don't
report correctly. What I need is a query which will tell me which items
are in the MASTER table but are NOT in the PRICING table. I don't need a
full list of contents, just a short list of NEW items.

I know I could do an append to the pricing table of the new items in the
master table, but I want to know which the items are first.

Any help appreciated?

Thanks

 

[Al Camp]

Keith,
Did you try my query suggestion? Any luck?
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Yes, as new products are launched, I often do not get informed until after
the event !! I therefore have more products than Prices !

I thought a primary rule of database design is non duplication of data.
Shouldn't he have either a replication database or a network connection to
a single master table.

John

John wrote:

Why do you have two tables. It's not good practice to have duplicate
tables with the same fields.

JOhn

Apparently, the OP has two similar tables because their contents come
from different sources: one [MASTER] table with catalog information,
that he maintains; and another Table with daily transactions, that
somebody else maintains, which the OP must reconcile with [MASTER]. And
my guess is that the records aren't totally identical; chances are that
[MASTER] contains some additional information that the daily sales
records do not contain.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.



I have a database which has a list of products and prices in one table.
I have another table which is updated each day from a file of new
sales. If a new product is sold which is not in my pricing table then I
don't report correctly. What I need is a query which will tell me which
items are in the MASTER table but are NOT in the PRICING table. I don't
need a full list of contents, just a short list of NEW items.

I know I could do an append to the pricing table of the new items in
the master table, but I want to know which the items are first.

Any help appreciated?

Thanks

 

[Vincent Johns]

I thought a primary rule of database design is non duplication of data.
Shouldn't he have either a replication database or a network connection to a
single master table.

John

Well, I agree with that, but I don't see any duplicate data here (at
least not before he copies them). Duplicating a table design isn't the
same thing. Acutally, from the OP's description, even the table designs
might not be isomorphic, though there are probably several fields which
have identical formats.

However, of course there are valid reasons for multiple users to share
and update a single table. But in this case they may not obtain (I
actually don't know enough about his situation to say for sure).

Perhaps the OP verifies each new day's data before copying them to the
master database (or he wishes to do so). After copying them, and maybe
archiving the transactions, he can empty the daily-transaction table.

Incidentally, if you're a purist about not duplicating any data, how
would you justify archiving anything? As a believer in Murphy's Law, I
personally tend to be kind of fussy about keeping adequate backup copies
around!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

John wrote:

Why do you have two tables. It's not good practice to have duplicate
tables with the same fields.

JOhn

Apparently, the OP has two similar tables because their contents come from
different sources: one [MASTER] table with catalog information, that he
maintains; and another Table with daily transactions, that somebody else
maintains, which the OP must reconcile with [MASTER]. And my guess is
that the records aren't totally identical; chances are that [MASTER]
contains some additional information that the daily sales records do not
contain.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I have a database which has a list of products and prices in one table. I
have another table which is updated each day from a file of new sales. If
a new product is sold which is not in my pricing table then I don't
report correctly. What I need is a query which will tell me which items
are in the MASTER table but are NOT in the PRICING table. I don't need a
full list of contents, just a short list of NEW items.

I know I could do an append to the pricing table of the new items in the
master table, but I want to know which the items are first.

Any help appreciated?

Thanks