Solved How to run an append query to append Five lines same data in the same table

[nector]

I have a product table which hold products for six warehouses. The business rule here says each of the six warehouses must have the same products name, but the difference should be the primary key or differentiated by the primary key. The reason being each warehouse represents a branch listed as below:

(1) 001
(2) 002
(3) 003
(4) 004
(5) 005
(6) 006

Now when a new product is created in branch 001, we need that new product to be pasted or replicated in all branches as below:

(1) 002 =======> Branch 002
(2) 003 =======> Branch 003
(3) 004 =======> Branch 004
(4) 005 =======> Branch 005
(5) 006 =======> Branch 006

So, in short, the appending query must append line 1 for 001, line 2 for 002, line 3 as 003-line 4 004 line 5 as 005 and Line 6 as 006 same newly created product, let us say created in 001 must be replicated in 002,003,004,005 and 006.

The below query can only do that replication one branch at a time, but users want it to be doing in all the branches at one run.

INSERT INTO tblProducts ( WHID, ItemID, ProductName, ReorderLevel, CountryOrigin, QuantityUnit, ProductType, PackageNet, Barcode, dftPrc, Codeclassification, itemClsCd, Sales, UOM, TPIN, bhfId, itemCd, itemTyCd, CountryCode, orgnNatCd, qtyUnitCd, btchNo, isrcAplcbYn, useYn, regrNm, regrId, modrNm, SupplierTPIN, specialCode, TaxClass, vatCatCd, TourismClass, taxAmtTl, PackageNetCode, pkgUnitCd, modrId )
SELECT [Forms]![frmProducts]![boBranchPKID] AS WHIDss, tblProducts.ItemID, tblProducts.ProductName, tblProducts.ReorderLevel, tblProducts.CountryOrigin, tblProducts.QuantityUnit, tblProducts.ProductType, tblProducts.PackageNet, tblProducts.Barcode, tblProducts.dftPrc, tblProducts.Codeclassification, tblProducts.itemClsCd, tblProducts.Sales, tblProducts.UOM, tblProducts.TPIN, [Forms]![frmProducts]![CboBranchesAdding] AS bhfIds, tblProducts.itemCd, tblProducts.itemTyCd, tblProducts.CountryCode, tblProducts.orgnNatCd, tblProducts.qtyUnitCd, tblProducts.btchNo, tblProducts.isrcAplcbYn, tblProducts.useYn, tblProducts.regrNm, tblProducts.regrId, tblProducts.modrNm, tblProducts.SupplierTPIN, tblProducts.specialCode, tblProducts.TaxClass, tblProducts.vatCatCd, tblProducts.TourismClass, tblProducts.taxAmtTl, tblProducts.PackageNetCode, tblProducts.pkgUnitCd, tblProducts.modrId
FROM tblProducts
WHERE (((tblProducts.ProductID)=[Forms]![frmProducts]![CboItemsAddToBranches]));

 

[plog]

First, I don't think your products table is a products table, it's an inventory table (e.g. QuantityUnit, WHID). You are storing a ton of duplicate data that should go into an actual products table (BarCode, ProductType etc.)I think you do need a products table--one that just lists all the info of the product itself regardless of location or quantity. Then another inventory table to assign products to warehouses and tracks their quantitites if that is in fact what you are doing.

Now, for your actual issue--I don't have a great grasp on it. The answer without digging deeper is to put a button on a form and have VBA run a series of APPEND queries behind the scenes so that it does all you need but the user only does one thing. But, I'm not sure that's the best way to achieve what you want, because I can't see the ultimate aim. Are you setting initial inventories with this? Or is this a hack around your poor structure as illustrated above? My gut says this might not be necesssary at all if you fix your table structure.

 

[cheekybuddha]

Your description gives the feeling that you have a flaw in your tables design.

If all products are available to all warehouses then you should have a single products table and a separate table for warehouses, and then a junction table for WarehouseProducts.

Then, when you get a new product you would do the equivalent of:

INSERT INTO WarehouseProducts
  (WHID, ItemID)
SELECT
  w.WHID, p.ItemID
FROM Warehouses w, Products p
WHERE p.ItemID = NewItemID

All the other info lives in the product table and can be retrieved with a query joining the tables

 

[DHookom]

I have a product table which hold products for six warehouses. The business rule here says each of the six warehouses must have the same products name, but the difference should be the primary key or differentiated by the primary key. The reason being each warehouse represents a branch listed as below:

(1) 001
(2) 002
(3) 003
(4) 004
(5) 005
(6) 006

Now when a new product is created in branch 001, we need that new product to be pasted or replicated in all branches as below:

(1) 002 =======> Branch 002
(2) 003 =======> Branch 003
(3) 004 =======> Branch 004
(4) 005 =======> Branch 005
(5) 006 =======> Branch 006

So, in short, the appending query must append line 1 for 001, line 2 for 002, line 3 as 003-line 4 004 line 5 as 005 and Line 6 as 006 same newly created product, let us say created in 001 must be replicated in 002,003,004,005 and 006.

The below query can only do that replication one branch at a time, but users want it to be doing in all the branches at one run.

INSERT INTO tblProducts ( WHID, ItemID, ProductName, ReorderLevel, CountryOrigin, QuantityUnit, ProductType, PackageNet, Barcode, dftPrc, Codeclassification, itemClsCd, Sales, UOM, TPIN, bhfId, itemCd, itemTyCd, CountryCode, orgnNatCd, qtyUnitCd, btchNo, isrcAplcbYn, useYn, regrNm, regrId, modrNm, SupplierTPIN, specialCode, TaxClass, vatCatCd, TourismClass, taxAmtTl, PackageNetCode, pkgUnitCd, modrId )
SELECT [Forms]![frmProducts]![boBranchPKID] AS WHIDss, tblProducts.ItemID, tblProducts.ProductName, tblProducts.ReorderLevel, tblProducts.CountryOrigin, tblProducts.QuantityUnit, tblProducts.ProductType, tblProducts.PackageNet, tblProducts.Barcode, tblProducts.dftPrc, tblProducts.Codeclassification, tblProducts.itemClsCd, tblProducts.Sales, tblProducts.UOM, tblProducts.TPIN, [Forms]![frmProducts]![CboBranchesAdding] AS bhfIds, tblProducts.itemCd, tblProducts.itemTyCd, tblProducts.CountryCode, tblProducts.orgnNatCd, tblProducts.qtyUnitCd, tblProducts.btchNo, tblProducts.isrcAplcbYn, tblProducts.useYn, tblProducts.regrNm, tblProducts.regrId, tblProducts.modrNm, tblProducts.SupplierTPIN, tblProducts.specialCode, tblProducts.TaxClass, tblProducts.vatCatCd, tblProducts.TourismClass, tblProducts.taxAmtTl, tblProducts.PackageNetCode, tblProducts.pkgUnitCd, tblProducts.modrId
FROM tblProducts
WHERE (((tblProducts.ProductID)=[Forms]![frmProducts]![CboItemsAddToBranches]));

Without judging your table structure, I would add the warehouse table to your append query with no join. Then pull the WHID from the warehouse table and exclude the [Forms]![frmProducts]![boBranchPKID].

 

[Uncle Gizmo]

I wondered if this might be of interest

In this blog on my website I demonstrate how you can add multiple records in a subform. Basically it duplicates the selected record.

Subform Record Duplicator​

Subform Record Duplicator - Nifty Access

Subform Record Duplicator Nifty Access - "Subform Record Replicator" allows you to provide your user with the facility to quickly add sets of duplicate records in a subform. All they need do is specify the number of Records they want to add, select a record to copy, and hey presto you've got a...

niftyaccess.com

rereading your question this might be more of a match to your problem

Recordset Loop - Create Records​

Create Many Records - Nifty Access

Recordset Loop - Create Records Becoming an MS Access Power User ????Once you start converting queries into SQL statements – in other words, a string version of the query – you can take that string and manipulate it, extract things from it, and do all sorts of things with it! One of the best...

niftyaccess.com

 

[Ken Sheridan]

I have a product table which hold products for six warehouses. The business rule here says each of the six warehouses must have the same products name, but the difference should be the primary key or differentiated by the primary key.

If you are simply registering each product to each warehouse, rather then modelling inventory, i.e. there are no non-key attributes of the relationship type, then you do not need to model the relationship type by a 'junction' table. You can simply return the Cartesian product of the two tables:

SELECT WHID, ProductID
FROM Warehouses, Products;

The key of the query's result table is a composite of WHID and ProductID, so fulfils your differentiating the instances of each product per warehouse. If you are really looking to model inventory then you'd INSERT each row returned by the above into a table, with the addition of a Quantity column, into which you'd insert a literal zero as the initial value, pending any transactions inserting stocks of the products . To insert rows for a new product only the SQL statement would be restricted to the product's ProductID value.

 

[nector]

Your description gives the feeling that you have a flaw in your tables design.

If all products are available to all warehouses then you should have a single products table and a separate table for warehouses, and then a junction table for WarehouseProducts.

Then, when you get a new product you would do the equivalent of:

INSERT INTO WarehouseProducts
  (WHID, ItemID)
SELECT
  w.WHID, p.ItemID
FROM Warehouses w, Products p
WHERE p.ItemID = NewItemID

All the other info lives in the product table and can be retrieved with a query joining the tables

Many thanks I think your idea will surely work here

 

[Ken Sheridan]

...............I think your idea will surely work here

Does the relationship between warehouses and products have any non-key attributes, e.g. QuantityOnHand? If so you will need to include a column for this in the 'junction' table which models the relationship. The column should be included in the parenthesized column list in the INSERT INTO clause, and 0 should be added to the column list in the SELECT clause.

If there are no such non-key attributes, then you don't need a 'junction' table at all. The result table of a query which returns the Cartesian product of the warehouses and products tables is all that's required, and will automatically be kept up to date in the event of rows any rows being added to or deleted from the warehouses and products tables.

Note, BTW, that, when managing inventory, it is not actually necessary to have a QuantityOnHand column in a base table. This can be computed by subtracting the sum of all transactions out of stock per product from the sum of all transactions per product into stock, with adjustments arising from periodic stocktakes. The attached little demo file illustrates the basic mechanisms for this.