UtterAccess.com
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
> Append Multiple Entries From A Field Value, Access 2016    
 
   
RobsBiz
post Aug 19 2019, 08:14 AM
Post#1



Posts: 3
Joined: 19-August 19



I would really appreciate any pointers with this! I would like to append multiple entries from one table to another table based on the value of one of the fields in the originating table.
Eg table1 = CLIENT, ITEM, MEASURE, WEIGHT, COUNT append to table 2 = CLIENT, ITEM, MEASURE, WEIGHT X [COUNT]

Many thanks in advance.
Go to the top of the page
 
mike60smart
post Aug 19 2019, 08:22 AM
Post#2


UtterAccess VIP
Posts: 13,349
Joined: 6-June 05
From: Dunbar,Scotland


Hi

welcome2UA.gif

Why do you have the same field names in 2 tables?

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Jeff B.
post Aug 19 2019, 08:45 AM
Post#3


UtterAccess VIP
Posts: 10,275
Joined: 30-April 10
From: Pacific NorthWet


To paraphrase Mike's question, what will having (?multiple?) copies in table2 of a record in table1 then allow you/your application to do? If we understand your business need, we may be able to offer better suggestions.

Also, in a well-normalized relational database, I'm not sure what value is added by having two tables with the same fields (per Mike's question).


--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2019, 08:46 AM
Post#4


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


I'm wondering the same thing as Mike. It's quite rare to have a situation where identical tables exist in a relational database. In fact, that's often considered a sign of an improperly designed set of tables.

It's a standard INSERT query:

SQL
INSERT INTO table2 (CLIENT, ITEM, MEASURE, WEIGHT, COUNT)
SELECT table1.Client, table1.Item, table1.Measure, table1.Weight, table1.Count
FROM table1 WHERE table1.Somefieldintable1 = SomeCriteria


But before we go down that path, we need to be sure it's an appropriate thing to be doing. For example, is this an audit table? If not, it's hard to think of a valid situation where this would need to be done in an appropriately designed relational database.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobsBiz
post Aug 19 2019, 10:02 AM
Post#5



Posts: 3
Joined: 19-August 19



the application I am building is to generate labels, which would be printed from the temporary records of table 2 which holds of batches of label information, table 1 holds the key information to create a batch of records into table 2.



eg
(batch)
CUSTOMER1, ITEM3, KG, 2
CUSTOMER1, ITEM3, KG, 2
CUSTOMER1, ITEM3, KG, 2
CUSTOMER1, ITEM3, KG, 2
(batch)
CUSTOMER2, ITEM1, EACH, 1
CUSTOMER2, ITEM1, EACH, 1
CUSTOMER2, ITEM1, EACH, 1
CUSTOMER2, ITEM1, EACH, 1


This would be primary information for the job which can consist of multiple batches.

I will be adding recipes/contents, allergen, storage and expiration information depending on the ITEM for each label.

I will remove the contents of TABLE 2 after printing the labels.

many thanks for such quick responses and interest.

Rob
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2019, 10:17 AM
Post#6


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Of course, temp tables for special tasks....

Well, the SQL template offered should get you started, I would expect. You just need to know what criteria to apply and where and how to define the value(s) to be used in the criteria. Often, we use a control on a form for that. Select an item in a listbox or combobox for that value. Use that control name in the query:

WHERE table1.Somefieldintable1 = Forms!frmLabelMakerForm.cboSelectaValidValueforThisCriteria

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobsBiz
post Aug 20 2019, 06:57 AM
Post#7



Posts: 3
Joined: 19-August 19



It has been a while since I built an application and I am rusty. But I'm pleased to say that I have solved the problem.

I created two queries one to append the record to table 2 and the second to reduce the value of the field for the number of records required in table 1.

I then looped them in a macro to the value of the number of records required.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 03:32 AM