Inventory Control Query?

[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.

 

[pietlinden]

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.

first things first. You need an inventory template. Allen Browne has
one here...
http://www.allenbrowne.com/AppInventory.html

IF you need more help, post a follow up question.

 

[Suzann]

Thanks for this link - I will definitely incorporate it into my database. It
will help when we do inventory. However, I'm not sure if it will solve the
whole problem I'm faced with. At period close, our auditors want to know
that we satisfy the document control requirement. Since these cards are
basically a reloadable credit card, we need to show we have good control of
the inventory. Therefore, in addition to needing to know the quantity each
unit has on hand, I also need a way of knowing what Card Serial #'s each
unit is left with so we, or our auditors, can go to any unit and say,
"According to inventory records, I show you have not sold Card Serial #
333333333333337578, please show this card to me." In other words, I need a
way to know what Card numbers each store should have at any given point - for
document control purposes. Knowing the quantity they should have will come
in handy during spot inventories. Knowing the specific card serial number
will be beneficial in tracking down any discrepancies - and will help us be
in accordance with GAAP.

 

[pietlinden]

Thanks for this link - I will definitely incorporate it into my database. It
will help when we do inventory.  However, I'm not sure if it will solvethe
whole problem I'm faced with.  At period close, our auditors want to know
that we satisfy the document control requirement.  Since these cards are
basically a reloadable credit card, we need to show we have good control of
the inventory.  Therefore, in addition to needing to know the quantity each
unit has on hand, I also need a  way of knowing what Card Serial #'s each
unit is left with so we, or our auditors, can go to any unit and say,
"According to inventory records, I show you have not sold Card Serial #
333333333333337578, please show this card to me."  In other words, I need a
way to know what Card numbers each store should have at any given point -for
document control purposes.  Knowing the quantity they should have will come
in handy during spot inventories.  Knowing the specific card serial number
will be beneficial in tracking down any discrepancies - and will help us be
in accordance with GAAP.

If you need to track every single serial number then, yes, you need to
put the serials in a single table. The easiest way to do this if the
numbers run in sequences (all values between A and B) is to create an
unbound form where the user enters the starting sequence and the
ending sequence and then you have a for loop that increments and then
adds the next value to a table using a recordset.

dim lngStartValue as long
dim lngEndValue As Long
dim lngCardID as long
dim rs as DAO.recordset

set rs=currentdb.Tabledefs("CardSerials",dbAppendOnly)

for lngCardID = lngStartValue To lngEndValue
rs.AddNew
rs.Fields("CardSerialNumber") = lngCardID
rs.Update
Next lngCardID

rs.close

set rs=nothing