X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How To Add Multiple Records Based On Quantity?, Access 2013    
post May 7 2015, 01:05 PM

Posts: 4
Joined: 7-May 15

Attached File  Evergreening_2.0.zip ( 74.71K )Number of downloads: 16

Hello Folks!

I'm working on a database for tracking inventory. The first part of the process is to place an Order for products. I want the user to enter a single Model and a quantity, then have Access create the corresponding number of records.

So, if we order 6 Model XYZ widgets, access would create six records.

The idea is that in the next step, Receiving, the widgets haven't yet been assigned Serial Numbers, so the receiver can pull up all products that do not have a Serial Number and enter it.

Does that make sense?

See attachment for details, and thank you in advance for any assistance!

Go to the top of the page
tina t
post May 7 2015, 01:28 PM

Posts: 6,465
Joined: 11-November 10
From: SoCal, USA

yes, it's clear enough, i think. i'm wondering if it's the best solution, though. creating placeholder records is generally not a good idea, because you have to do so much extra work to manage them. what if a quantity is decreased after the original order is logged? now you have extra records to get rid of. what if the supplier only fills the order with a partial shipment, and notifies you at some point that the order cannot be completed (discontinued product, for instance). again, extra records to get rid of. what if the supplier over-ships, and your Purchasing dept decides to keep the extras; will the Receiving dept have access to create additional records to cover the extra items?

if all your inventory items get (or have) specific serial numbers, then you should probably have a child table (to parent table InventoryItems) to list serial numbers and any other data that describes a specific item. then your Receiving dept only needs to look up the inventory item Model XYZ widget (or add it, if it's new to inventory) and enter a record in the related child table for each serial number.

Go to the top of the page
post May 7 2015, 01:34 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


You could use run a simple Append query within a loop. Here's a subroutine you could call:

Public Sub AddNewRecords(ByVal intQty As Integer, lngOrder As Long, lngItem As Long)

    Dim strSQL As String
    While intQty > 0
        strSQL = "INSERT INTO tblOrderedItems( lngOrderID, lngItemID ) " & _
                 "VALUES (" & lngOrder & ", " & lngItem & ");"
        CurrentDB.Execute strSQL, dbFailOnError
        intQty = intQty - 1
End Sub

The syntax would be:

AddNewRecords(5, 372, 112)

This will add 5 new records to the table named "tblOrderedItems", using the "lngOrderID" and "lngItemID" fields to identify the order and the item being ordered.

Even if this isn't exactly what you need, it should be easy enough to adapt.

Hope this helps,

Go to the top of the page
post May 7 2015, 03:03 PM

Posts: 4
Joined: 7-May 15

Thank you both for your input. Much appreciated!
Go to the top of the page
post May 7 2015, 03:15 PM

Posts: 3,585
Joined: 27-February 09

My favorite way to do this is with a Tally or "Numbers" table.

just a series of numbers from 1 to (something)... maybe 10.


INSERT INTO Tally (N) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Then you can do something like

SELECT Items.Name, Items,Qty, Tally.N
FROM Items INNER JOIN Tally ON Items.Qty>=Tally.N

(look Ma, no code!)
Go to the top of the page
post May 11 2015, 10:06 AM

Posts: 4
Joined: 7-May 15

I've been playing with the previous suggestions, but to be honest I'm not terribly familiar with coding. Perhaps I should provide a better explanation of what I'm trying to accomplish.

I am working on a database that will allow a small community college to Order, Receive, Install, and "Evergreen" IT hardware. Evergreen refers to assigning a Service Life and End of Life to each Model so it can be removed from service and replaced.

I've created tbl_Model with the following fields:
Model_PK (autonumber)
DeviceType_FK (Computer, Switch, Laptop, etc.)
Make_FK (Panasonic, Sanyo, Cisco, Dell, etc.)
Warranty (in Years)
Service Life (in Years)
End of Life (in Years)

When a Model is ordered, the user does not know the Serial Number and other details, so I've broken that out into tbl_Device.
Device_PK (autonumber)
Serial Number
Rcv'd Date
Purchase Price
Installation Date
Quantity (Number of items ordered. I've created a Numbers Table [tbl_Numbers] for this value, as suggested.)

I have qry_Order which uses the following SQL:

SELECT tbl_Device.Model_PK, tbl_DeviceType.[Device Type], tbl_Make.Make, tbl_Model.Warranty, tbl_Model.[Service Life], tbl_Model.[End Of Life], tbl_Device.[Purchase Price], tbl_Device.Quantity
FROM (tbl_Make INNER JOIN (tbl_DeviceType INNER JOIN tbl_Model ON tbl_DeviceType.DeviceType_PK = tbl_Model.DeviceType_PK) ON tbl_Make.Make_PK = tbl_Model.Make_PK) INNER JOIN tbl_Device ON tbl_Model.Model_PK = tbl_Device.Model_PK;

What I'd like to do is have Access generate x number of records in tbl_Device based on the Quantity. This may have already been explained in the previous responses, however, I am not a coding guru by any stretch, so I'm hoping someone can explain, in fairly simplistic terms, how I would set this up, or suggest a better approach.

I apologize in advance for my 'newbie-ness'! smile.gif

Edit: Moved the Quantity field from tbl_Model to tbl_Device.
Go to the top of the page
post May 11 2015, 11:54 AM

Posts: 3,585
Joined: 27-February 09

I built you a simple example. Right now it's running a simple select query so you can see what the query does. The one snag you may run into is if the table you're using this to add records to has a non-autonumber primary key, like a Serial Number. In that case, you could add the Serial Number as a text field and make it UNIQUE but allow nulls.

The demo shows how the Tally table works. Basically, if you join two tables that have no join between them, you get a Cartesian product, all the values in table A multiplied by all the values in table B.
Go to the top of the page
post May 11 2015, 02:20 PM

Posts: 4
Joined: 7-May 15

Thank you very much MadPiet. That does give me a better understanding of how to use the Tally table. I'm going to play around with this today and see what I can work out.
Go to the top of the page
post Mar 24 2020, 09:48 AM

Posts: 1
Joined: 24-March 20

Hello Doctor 9
Can you make this example in a small database and upload it here.
I cant understand the concept as I am new in access
thanks a lot in advance
This post has been edited by GeorgeSobhy: Mar 24 2020, 09:49 AM
Go to the top of the page
post Mar 24 2020, 11:20 AM

UA Admin
Posts: 37,007
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

Unfortunately, the member who created this code has since passed away.

In addition, it's not clear what you would want to see in such a sample.
This post has been edited by GroverParkGeorge: Mar 24 2020, 11:21 AM

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Mar 24 2020, 11:45 AM

Posts: 3,585
Joined: 27-February 09

you don't need ANY VB code for this. None. Nada. if you have a Numbers table, you can just create a non-equi join between the two tables.

Say I have a table of products to make:
CREATE TABLE MakeMe (ProductID INT, Qty TINYINT, InvoiceNo INT);

INSERT INTO MakeMe VALUES (100, 10, 20001),(102, 50, 20001);

Then if you have a table of "numbers" from 1 to your maximum Qty), it's just

SELECT ProductID, InvoiceNo,
FROM MakeMe m INNER JOIN Tally t ON m.Qty>= t.Num;

And it scales. VBA doesn't..
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    8th April 2020 - 09:58 PM