473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Populating one field in a table with one to many relation

Hi,

I have a main table named tblCrPurchase2 with following fields
CrPurchase2ID - key field
InvoiceDate
InvoiceNumber
PF1

I have a child table called tblCrPurchase2Detail (this is the many side
of the table) with following fields
CrPurchase2DetailID - Key field
CrPurchase2ID - for linking with tblCrPurchase2 table
ProdDesc
PurPr2
PurQty2
PF2

Figure 1
Data for tblCrPurchase2 is:
CrPurchase2ID InvoiceDate InvoiceNumber PF1
1 7/1/2005 1245 0.10
2 7/5/2005 32154 0.25

Figure 2
Data for tblCrPurchase2Detail is
CrPurchase2ID CrPurchase2Detail ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0
1 2 prod2 5.35 0
1 3 prod3 3.65 0
2 4 prod1 1.35 0
2 5 prod2 5.00 0
2 6 prod3 3.65 0

I want to update the first three records with PF2 = 0.10 and the last
three records with PF2 = 0.25 so end result would look like this
Figure 3
CrPurchase2ID CrPurchase2Detail ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25

By using a command button I put the following code on click event

Private Sub cmdMakechanges_Click()
Dim db As Database
Dim rs As Recordset
Dim FirstPF As Double

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM tblCrPurchase2 INNER JOIN
tblCrPurchase2Detail ON tblCrPurchase2.CrPurchase2ID =
tblCrPurchase2Detail.CrPurchase2ID", dbOpenDynaset)

FirstPF = rs!PF1

rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!PF2 = FirstPF
rs.Update
rs.MoveNext
Loop

rs.Close

End Sub
Figure 4
After running this code my query get populated as follows
CrPurchase2ID CrPurchase2Detail ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.10
2 5 prod2 5.00 0.10
2 6 prod3 3.65 0.10

What I really want done is if I am at nth record of main table than the
code should pull up the data from many side of the table that relates
with one side of the table and populate PF2 with whatever is in PF1.
For example if I am at record 2 of main table with PF1 = 0.25 than it
should populate PF2 = 0.25 in many side of the table in only three
records. I.e record 4,5, and 6. because record 4,5, and 6 in many side
of table is related to record 2 on one side of table as the table shows

Figure 5
CrPurchase2ID CrPurchase2Detail ProdDesc PurPr2 PF2
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25
I hope I have made myself clear.

How do I code to get the results as shown?

Thanking you all in advance

Nov 13 '05 #1
2 1607
1) do you need to save a value that you can derive from existing data?
2) can you use an update query instead of code? Most of the times this
is more reliable and executes (way) faster

ha*******@yahoo.com wrote:

I want to update the first three records with PF2 = 0.10 and the last
three records with PF2 = 0.25 so end result would look like this
Figure 3
CrPurchase2ID CrPurchase2Detail ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25


--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #2
Do you really need to do it in code? You could just run an update
query:

UPDATE tblCrPurchase2
SET ProdDesc = 0.10
WHERE CrPurchase2ID = 1

.... actually, you could dynamically create the query in code and
execute it there if you need to - probably easier (and faster running)
than messing with recordsets.

docmd.runsql strMyUpdateQuery

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Svelte Poshy | last post by:
Populating a field in a table depending on another In my table customers i want to populate the field segmenid depending on the choice in the lookup field Typeid. Both fields, typeid and...
2
by: Megan | last post by:
Hi everybody- I have 2 tables, Hearings and Rulings. Both have primary keys called, CaseID, that are autonumbers. I don't want both tables to have the same autonumber. For example, if Hearings...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
3
by: sck10 | last post by:
Hello, I am creating a form for users to enter information about a lab and the members of the lab. I have one form (FormView) that they use to enter information about that lab. The keyvalue is...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
39
by: windandwaves | last post by:
Hi Folk I have to store up to eight boolean bits of information about an item in my database. e.g. with restaurant drive-through facility yellow windows
4
by: Don Do | last post by:
Help I built a form/subform/subsubform setup using the access forms wizard. I have a table1 = parent, table2 = child, table3 = (grandchild?). There will be multiple records in table2 that tie...
3
by: CourtGuy | last post by:
Hi Folks, I've got a problem that's been confounding me for months. I work for a criminal court, and I'm trying to set up an Access database to track petitions filed by criminal defendants. ...
4
bugboy
by: bugboy | last post by:
I'm inserting a new word into table 'w' and a definition into table 'c' which are linked in table 's' which is the relation table for the many to many relationship between 'w' and 'c'. I've been...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.