Conceptual Problem - Comparing data in to tables

  • Thread starter Thread starter Cool Dude
  • Start date Start date
C

Cool Dude

Hi Folks,

There MUST be an easier way to do this... but I'm struggling... any help
would be appreciated.

I've got a database with two tables.

Table 1 has a list of records - things that I bought last time I went
shopping at various electronics stores with the date that they were bought
on.

Tsble 2 is the record of the newest trip to the electronics store.

What I want to do is compare the records in both tables, and if the
descrption field is the same in both (i.e. I bought the same thing on two
different occasions) then just amend the quantity and date in the first
table. If the record is different, then add the record from the second to
the first.

I can write a vba script that compares every record in the first to every
record in the second... but

a) I'm just thinking that there must be an easier way!

b) Even then, while it WORKS, it does TOO MUCH work (and therefore takes
longer).

for example, if I have this data

table 1 description
----------------------------

capacitors - small
capacitors - medium
capacitors - large
resistors - small
transistors - small

table 2 description
-----------------------------

capacitors - large
capacitors - small
transistors - large
transistors - small

The second table is always in alpha order (electronically provided by the
supplier) but I can't figure out how to stop the comparison of something
that starts with the letter 'c' with only those things that start with the
letter 'c' (aside from explicitly doing it - i.e. left(description,1)

So, right now, my code compares capacitors with transistors... and yet I
know anything that starts with 'c' isn't ever going to match anything that
starts with 't' (and really it should stop when it gets to 'd'... )

hope that makes some sense. please let me know if it doesn't.

any help would be appreciated..

thanks

-JB
 
well, assuming that the description text of two identical items will
actually be identical - in other words, not calling an item a capacitor one
time and using another description another time, or misspelling it as
capaciter another time - you can write a SELECT query that links the two
tables on the description field with an INNER JOIN. the query will only
return matching records. once you get that working to your satisfaction, you
can use the query as the basis for subsequent action; if it returns a match,
then you can probably use an update query to update the quantity and date in
the first table; if it doesn't return a match, then use an append query to
add the records to the first table.

hth
 
Back
Top