Make table from a range between 2 fields in another table

[Suzann]

I have a table that lists information for a gift card program we've initiated
at my workplace. The basic format is:

Date Store Product BegSerialNo EndSerialNo
7/3/8 137 WAB1 1001 1250
7/3/8 269 WAB1 1251 1500

Since we have almost 600 stores and are shipping cards to each store in 250
lots, I need to make a table that has all the serial numbers listed for cards
that have been issued. The new table structure I have set up is:

SerialNo Store
1001 137
1002 137

I will run an unmatched query on this table against issuances by stores -
another table that I have set up. I received wonderful advice on how to set
up a template to know the quantity on hand at any given point last week from
this forum. However, for accounting control purposes, I need to be a way to
know what card serial numbers a store should still have in stock at year end
inventory time so we can do a spot check. I've tried a combination of Insert
Into with a looping structure but cannot get it to work. I appreciate all
the helpful advice I receive from this forum.

 

[Allen Browne]

You need a counting table to provide these numbers.

Set up a table with a Number field, and enter all the numbers you need.
There's code here so you don't have to do that manually:
http://allenbrowne.com/ser-39.html

Now create a query that uses your existing one and tblCount. There must be
no line joining the 2 tables in the upper pane of query design. This gives
every possible combination (a Cartesian product.)

In the Criteria row under the CountID field, enter:
Between [BegSerialNo] And [End SerialNo]
This limits the query so it gives only the desired serial numbers for each
record.

 

[Suzann]

Thank you so much - I'll try this right now. By the way, it was your
quantity on-hand code that helped me previously. I truly appreciate all of
you taking time to help and point us in the right direction.

You need a counting table to provide these numbers.

Set up a table with a Number field, and enter all the numbers you need.
There's code here so you don't have to do that manually:
http://allenbrowne.com/ser-39.html

Now create a query that uses your existing one and tblCount. There must be
no line joining the 2 tables in the upper pane of query design. This gives
every possible combination (a Cartesian product.)

In the Criteria row under the CountID field, enter:
Between [BegSerialNo] And [End SerialNo]
This limits the query so it gives only the desired serial numbers for each
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that lists information for a gift card program we've
initiated
at my workplace. The basic format is:

Date Store Product BegSerialNo EndSerialNo
7/3/8 137 WAB1 1001 1250
7/3/8 269 WAB1 1251 1500

Since we have almost 600 stores and are shipping cards to each store in
250
lots, I need to make a table that has all the serial numbers listed for
cards
that have been issued. The new table structure I have set up is:

SerialNo Store
1001 137
1002 137

I will run an unmatched query on this table against issuances by stores -
another table that I have set up. I received wonderful advice on how to
set
up a template to know the quantity on hand at any given point last week
from
this forum. However, for accounting control purposes, I need to be a way
to
know what card serial numbers a store should still have in stock at year
end
inventory time so we can do a spot check. I've tried a combination of
Insert
Into with a looping structure but cannot get it to work. I appreciate all
the helpful advice I receive from this forum.

 

[KARL DEWEY]

Why not just pull data from existing table like this --
SELECT Date, Store, Product, Max([EndSerialNo] AS Last_Serial_No
FROM YourTable
GROUP BY Date, Store, Product;

By the way, 'Date' is a reserved word in Access and therefore may give you
trouble.

 

[Suzann]

Would this give me a table listing each serial number sequentially? I
receive a spreadsheet from the supplier listing the serial number range and
quantity of cards they've shipped to each unit. I also receive from each
unit a spreadsheet listing the activations - supplying the serial number and
amount. For purposes of forms control, I need to know not only the quantity
on hand at any given point (that problem was solved on a previous post), but
also the serial numbers that a store should have in stock. Therefore, I
need a table listing all the serial numbers that have been issued to each
store. I'll do an unmatched query using this table with the activations. In
other words, I need to be able to say to a manager during inventory checks,
"I show you have 33 gift cards in stock; I also show you should have serial
number 1234 in stock - I need to count the cards you have - and also see that
specific card." The quantity on hand I was able to take care of by
suggestions from a previous post.

Thanks for your suggestion, I'll certainly try it - also, the actual field
name is ShipDate for the Issuance Table and IssueDate for the Activations, so
hopefully I'm safe there. I was just trying to give a short example of the
basic table structure.

Why not just pull data from existing table like this --
SELECT Date, Store, Product, Max([EndSerialNo] AS Last_Serial_No
FROM YourTable
GROUP BY Date, Store, Product;

By the way, 'Date' is a reserved word in Access and therefore may give you
trouble.
--
KARL DEWEY
Build a little - Test a little

I have a table that lists information for a gift card program we've initiated
at my workplace. The basic format is:

Date Store Product BegSerialNo EndSerialNo
7/3/8 137 WAB1 1001 1250
7/3/8 269 WAB1 1251 1500

Since we have almost 600 stores and are shipping cards to each store in 250
lots, I need to make a table that has all the serial numbers listed for cards
that have been issued. The new table structure I have set up is:

SerialNo Store
1001 137
1002 137

I will run an unmatched query on this table against issuances by stores -
another table that I have set up. I received wonderful advice on how to set
up a template to know the quantity on hand at any given point last week from
this forum. However, for accounting control purposes, I need to be a way to
know what card serial numbers a store should still have in stock at year end
inventory time so we can do a spot check. I've tried a combination of Insert
Into with a looping structure but cannot get it to work. I appreciate all
the helpful advice I receive from this forum.