S
Suzann
I need to develop a way to keep track of gift cards received and sold by our
company stores. We have 4 different card types and over 600 stores. I
receive an Excel spreadsheet from our supplier showing the cards that have
been issued to each store. It's in the following format (Card Numbers have
been changed, naturally, but they're 18 digits long):
Store Number Card Type Start Serial End Serial
B000002 Classic 333333333333337000 333333333333337500
B000002 Logo 333333333333339751 333333333333350000
B000003 A Frame 333333333333337501 333333333333377750
The report we download from the stores is in the following format:
MERCHANT # ACTION CARDHOLDER #
B000072 Activation/Issuance (New) 333333333333333045
B000005 Activation/Issuance (New) 333333333333333136
B000102 Activation/Issuance (New) 333333333333333750
B000031 Activation/Issuance (New) 333333333333333813
B000956 Activation/Issuance (New) 333333333333333836
B000683 Activation/Issuance (New) 333333333333333502
What I need to know are the card numbers that the stores should have in
stock at any given point in time - say at monthly or quarterly inventories.
Should I make a new table that has all the card numbers listed for each store
and then use a find unmatched query against the new table and the Issuance
table described above? If so, how would I make the new table showing the
card numbers and store number from the first table provided? I've used
queries in the past that have looked for data between two specified fields
but am stuck on how to make a table that would provide every single number
between the starting and ending serial numbers. Thank you for any help you
might be able to provide.
company stores. We have 4 different card types and over 600 stores. I
receive an Excel spreadsheet from our supplier showing the cards that have
been issued to each store. It's in the following format (Card Numbers have
been changed, naturally, but they're 18 digits long):
Store Number Card Type Start Serial End Serial
B000002 Classic 333333333333337000 333333333333337500
B000002 Logo 333333333333339751 333333333333350000
B000003 A Frame 333333333333337501 333333333333377750
The report we download from the stores is in the following format:
MERCHANT # ACTION CARDHOLDER #
B000072 Activation/Issuance (New) 333333333333333045
B000005 Activation/Issuance (New) 333333333333333136
B000102 Activation/Issuance (New) 333333333333333750
B000031 Activation/Issuance (New) 333333333333333813
B000956 Activation/Issuance (New) 333333333333333836
B000683 Activation/Issuance (New) 333333333333333502
What I need to know are the card numbers that the stores should have in
stock at any given point in time - say at monthly or quarterly inventories.
Should I make a new table that has all the card numbers listed for each store
and then use a find unmatched query against the new table and the Issuance
table described above? If so, how would I make the new table showing the
card numbers and store number from the first table provided? I've used
queries in the past that have looked for data between two specified fields
but am stuck on how to make a table that would provide every single number
between the starting and ending serial numbers. Thank you for any help you
might be able to provide.