If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Inventory Control Query?
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. |
#2
|
|||
|
|||
Inventory Control Query?
On Jul 1, 9:43*am, Suzann wrote:
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. |
#3
|
|||
|
|||
Inventory Control Query?
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. " wrote: On Jul 1, 9:43 am, Suzann wrote: 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. |
#4
|
|||
|
|||
Inventory Control Query?
On Jul 1, 1:16*pm, Suzann wrote:
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. " wrote: On Jul 1, 9:43 am, Suzann wrote: 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. 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 |
Thread Tools | |
Display Modes | |
|
|