Append Query (1 Viewer)

[georgesobhyy]

Hi
I am trying to append data from tableA to tableB using an append query
The problem is every time i run the query table A have the same records from table b again and again even i didnt add new records in tableB.
it is like a nightmare

 

[jdraw]

Suggest you tell us more about Tables A and B; and show us the SQL for your append query.
Seems you are adding the same records over and over - do you have any check to prevent duplicates?

 

[georgesobhyy]

Thx for the reply
Look I will explain exactly what i want to do
I have tbl products with some records and each record has quantity field
let say 3 records and the sum of quantity field is 8

I want to append these 3 records to another table with 8 records (one record for each product quantity)

and do that every time i add a record in tbl products

lets say i add a new record with the quantity of 2

So this will append two records in the other table and so on

 

[jdraw]

Hmmm? What exactly is the purpose for 2 tables? Perhaps you could describe what you are trying to achieve in simple English -no database jargon/tables etc. Once we understand WHAT you are trying to do, I'm sure readers will propose options for HOW it could be done with Access.
I'm not following the significance of "quantity" such that it is stored in a second table.
If you are subtracting from Inventory, then I could see the use of a second table for transactions etc, but at the moment things are not clear (to me at least).

Good luck with your project.

 

[georgesobhyy]

Ok
Lets say I have a project, this project has 7 doors, and the 7 doors are two different products, one product's quantity is 4 (4 doors) and the other's quantity is 3 (3 doors)
So Tblproducts has 2 records

ProductID ProductModel Qty
1 Model A 4
2 Model B 3

I want to append this records to TblDoors with 7 records

DoorID ProductModel ProductID
1 Model A 1
2 Model A 1
3 Model A 1
4 Model A 1
5 Model B 2
6 Model B 2
7 Model B 2

And every time I add records to tblProducts, it will be appebded to tblDoors (only new records , and not the old records again)

 

[jdraw]

Perhaps I'm missing the obvious?? Does DoorId have special meaning to you?
Can you not simply query tblDoors and get the info you are currently storing in tblProducts.

Select ProductModel, count(qty) as QTY
From tblDoors
Group By ProductModel

 

[georgesobhyy]

Yes
I want two things
- to automatically generate doors records from tblproducts
- the DoorID will be the door unique number

 

[jdraw]

So you will be assigning a unique doorID to each door/record you generate from tblProducts?
You will need a DoorID generator, and you will have to ensure that each newly generated DoorId does not exist in your tblDoors.

--very generic logic is a long this ---
Generate newDoorID from tblProducts
where newDoorID NOT IN
(Select doorId from tblDoors)

It might be helpful to you and readers if you could overview the entire business rather than just a small piece related to Doors. I have purchased doors in the past and there are usually more models and options than your brief posting would satisfy, but you know your project better than readers.
Good luck.