Creating Multiple Entries from Numeric Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
Is it possible to create multiple identical records in a select query from a
numeric variable generated separately?
I am in the process of creating shipping tickets, and depending on how many
items are being shipped, I will need to break the shipment into a different
number of packages.
I currently have a query that groups and creates a single ticket, but was
wondering if I could feed in a number (x) that the query would then break the
ticket into "x" number of entries?
Or would this need to be done in code? Any help or examples would be
greatly, greatly appreciated.
Thanks!
-gary
 
If I understand correctly your query might return one record but you need
multiple numbered copies of the same record. If this is correct, consider
creating a table of numbers [tblNums] with a single numeric field [num] and
values from 1 to 100.

If I wanted to create a query in the Northwind sample database of a single
order from the Orders table, my SQL would look like:
SELECT tblNums.Num, [Enter Number] AS TotalNumber, Orders.*
FROM Orders, tblNums
WHERE tblNums.Num<=[Enter Number] AND Orders.OrderID=10248;

I generally prefer to enter the quanitity in a control on a form rather than
a parameter prompt.
 
Hi Duane,
Thanks for the reply.
I will have the number (quantity of entries to be entered) generated ahead
of time, but it will either be done through code and set up as variable, or
from another query - not sure what will work better? Currently, to generate
shipments, I have a select query that gathers the information to be shipped,
and each order will get one record. I bring in the weight and quantity as
well, and depending on the criteria for each order, I will then be able to
determine which one should get multiple shipments (ie, if item quantity is
25, then split) I am thinking I need a first select query that will run
Click to expand...
these checks, and add in the number of shipments needed into a new field. I
then want to take this number, and be able to duplicate the shipment record
that many times into another query. Is that possible? Is that what the
number table will do?
Thanks again
-gary

Duane Hookom said:
If I understand correctly your query might return one record but you need
multiple numbered copies of the same record. If this is correct, consider
creating a table of numbers [tblNums] with a single numeric field [num] and
values from 1 to 100.

If I wanted to create a query in the Northwind sample database of a single
order from the Orders table, my SQL would look like:
SELECT tblNums.Num, [Enter Number] AS TotalNumber, Orders.*
FROM Orders, tblNums
WHERE tblNums.Num<=[Enter Number] AND Orders.OrderID=10248;

I generally prefer to enter the quanitity in a control on a form rather than
a parameter prompt.

--
Duane Hookom
Microsoft Access MVP


Gary Dolliver said:
Hi all,
Is it possible to create multiple identical records in a select query from a
numeric variable generated separately?
I am in the process of creating shipping tickets, and depending on how many
items are being shipped, I will need to break the shipment into a different
number of packages.
I currently have a query that groups and creates a single ticket, but was
wondering if I could feed in a number (x) that the query would then break the
ticket into "x" number of entries?
Or would this need to be done in code? Any help or examples would be
greatly, greatly appreciated.
Thanks!
-gary
Click to expand...
Click to expand...
 
It doesn't make much difference where the number of shipments come from as
long as it is available in the query. The numbers table solution is the same.
--
Duane Hookom
Microsoft Access MVP


Gary Dolliver said:
Hi Duane,
Thanks for the reply.
I will have the number (quantity of entries to be entered) generated ahead
of time, but it will either be done through code and set up as variable, or
from another query - not sure what will work better? Currently, to generate
shipments, I have a select query that gathers the information to be shipped,
and each order will get one record. I bring in the weight and quantity as
well, and depending on the criteria for each order, I will then be able to
determine which one should get multiple shipments (ie, if item quantity is
25, then split) I am thinking I need a first select query that will run
Click to expand...
these checks, and add in the number of shipments needed into a new field. I
then want to take this number, and be able to duplicate the shipment record
that many times into another query. Is that possible? Is that what the
number table will do?
Thanks again
-gary

Duane Hookom said:
If I understand correctly your query might return one record but you need
multiple numbered copies of the same record. If this is correct, consider
creating a table of numbers [tblNums] with a single numeric field [num] and
values from 1 to 100.

If I wanted to create a query in the Northwind sample database of a single
order from the Orders table, my SQL would look like:
SELECT tblNums.Num, [Enter Number] AS TotalNumber, Orders.*
FROM Orders, tblNums
WHERE tblNums.Num<=[Enter Number] AND Orders.OrderID=10248;

I generally prefer to enter the quanitity in a control on a form rather than
a parameter prompt.

--
Duane Hookom
Microsoft Access MVP


Gary Dolliver said:
Hi all,
Is it possible to create multiple identical records in a select query from a
numeric variable generated separately?
I am in the process of creating shipping tickets, and depending on how many
items are being shipped, I will need to break the shipment into a different
number of packages.
I currently have a query that groups and creates a single ticket, but was
wondering if I could feed in a number (x) that the query would then break the
ticket into "x" number of entries?
Or would this need to be done in code? Any help or examples would be
greatly, greatly appreciated.
Thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Awesome, thank you so much!!! This will work great!
-gary

Duane Hookom said:
It doesn't make much difference where the number of shipments come from as
long as it is available in the query. The numbers table solution is the same.
--
Duane Hookom
Microsoft Access MVP


Gary Dolliver said:
Hi Duane,
Thanks for the reply.
I will have the number (quantity of entries to be entered) generated ahead
of time, but it will either be done through code and set up as variable, or
from another query - not sure what will work better? Currently, to generate
shipments, I have a select query that gathers the information to be shipped,
and each order will get one record. I bring in the weight and quantity as
well, and depending on the criteria for each order, I will then be able to
determine which one should get multiple shipments (ie, if item quantity is
25, then split) I am thinking I need a first select query that will run
Click to expand...
these checks, and add in the number of shipments needed into a new field. I
then want to take this number, and be able to duplicate the shipment record
that many times into another query. Is that possible? Is that what the
number table will do?
Thanks again
-gary

Duane Hookom said:
If I understand correctly your query might return one record but you need
multiple numbered copies of the same record. If this is correct, consider
creating a table of numbers [tblNums] with a single numeric field [num] and
values from 1 to 100.

If I wanted to create a query in the Northwind sample database of a single
order from the Orders table, my SQL would look like:
SELECT tblNums.Num, [Enter Number] AS TotalNumber, Orders.*
FROM Orders, tblNums
WHERE tblNums.Num<=[Enter Number] AND Orders.OrderID=10248;

I generally prefer to enter the quanitity in a control on a form rather than
a parameter prompt.

--
Duane Hookom
Microsoft Access MVP


:

Hi all,
Is it possible to create multiple identical records in a select query from a
numeric variable generated separately?
I am in the process of creating shipping tickets, and depending on how many
items are being shipped, I will need to break the shipment into a different
number of packages.
I currently have a query that groups and creates a single ticket, but was
wondering if I could feed in a number (x) that the query would then break the
ticket into "x" number of entries?
Or would this need to be done in code? Any help or examples would be
greatly, greatly appreciated.
Thanks!
-gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top