Update table with different format

[Deutz]

Thanks in advance. I have two tables, table 1 and table 2 as per below. I want to update table 2 with values from the Cost and Parts fields in Table 1.

My question is, how would I match up the fields so that the data ends up in the right slots in Table 2?


Table 1:

ForeignKey...|Type...|Range...|Cost...|Parts
202..........|A......|1.......|$10....|120
202..........|A......|2.......|$50....|10
202..........|B......|1.......|$100...|30
202..........|B......|2.......|$5.....|0

Table 2 prior to data entered from Table 1:

ForeignKey...|FieldNm...|Range...|A...|B...
202..........|Cost......|1.......|....|....
202..........|Cost......|2.......|....|....
202..........|Parts.....|1.......|....|....
202..........|Parts.....|2.......|....|....

Desired result: Table 2 after data entered from Table 1:

ForeignKey...|FieldNm...|Range...|A.....|B...
202..........|Cost......|1.......|$10...|$100
202..........|Cost......|2.......|$50...|5
202..........|Parts.....|1.......|120...|30
202..........|Parts.....|2.......|10....|0


Rgds
Deutz

 

[vbaInet]

It's bad practice to save those values because they can be calculated.

Plus your tables don't look normalized.

 

[Deutz]

Thanks VBAInet for your reply,

What I'm trying to do, with little success, is display data from Table 1 in a subform but in a different orientation to the table (some colunms to rows and some rows to columns as per my example). I am trying to do this by rearranging the Cost and Parts data and dumping it into Table 2 (temp table) to which the subform is bound. When the user updates values in the subform they are automatically saved to the temp table and if the user then clicks the Save button on the parent form I want to transfer the values in the temp table back into the original format to update the right slots in Table 1. So it is really messy and any suggestions on how to do this or on a better approach would be appreciated. ???


Thanks
Deutz

 

[vbaInet]

If all you want to do is display the values, you can use a DLookup() function. Have you explored that option?

 

[Deutz]

I do want to display the values in the subform so I need a way to load the values from Table 1 into the subform/temp table and a way to save the values in the subform/temp table back into Table 1 in the right places.


Rgds
Deutz

 

[vbaInet]

You're breaking normalization rules by doing that and if your application becomes a multi-user database it will break instantly.

 

[Deutz]

Shouldn't be a problem using Table 2 as a temp table in a multiuser envirionment as this db is split with the temp table local to the front end (each user will have a copy). I think Table 1 is normalised correctly, unlike Table 2 which is de-normalised for subform display purposes.

So if this is not the way to do it then how should I proceed?

Rgds
Deutz

 

[vbaInet]

One of the ways I can see this happening is if you perform the operations in four folds.

Here's on more suggestion, why not display the records and allow editing in separate boxes above the subform. So anytime the row in the subform is clicked, you use VBA to automatically set the values of the textboxes above the subform to the current record clicked. The user can make changes in those textboxes.

 

[Deutz]

Sorry, don't know what you mean by 'four folds'.

I did start out with the idea to use separate text boxes on another form or above the subform for saving. The only problem I have with that solution is how do I know where to save each textbox value in the temp table, or if not using the temp table then in Table 1, as each textbox value does not map exactly to a field name in the table but is defined by a combination of column/row names etc. ?

 

[vbaInet]

Sorry, don't know what you mean by 'four folds'.

Four folds meaning you will need four lots of updates per insert. Not worth it

Let's progress on with this idea:

I did start out with the idea to use separate text boxes on another form or above the subform for saving. The only problem I have with that solution is how do I know where to save each textbox value in the temp table, or if not using the temp table then in Table 1, as each textbox value does not map exactly to a field name in the table but is defined by a combination of column/row names etc. ?

You have all the information you need to update the field. You've got the ForeignKey value, the field name from the FieldNm column, and the Range. A typical update statement would be:

... use code to ensure that the textboxes contain valid values before proceeding ...
DoCmd.RunSQL "UPDATE Table1 " & _
             "SET " & Me.SubformControl.FieldNmControl & " = " & Me.CostOrPartsTextbox & _
             "WHERE ForeignKey = " & Me.ForeignKTextbox & " AND Range = " & Me.RangeTextbox & ";"

 

[Deutz]

I'm almost there but still a little unsure of how to get the value from the textboxes to Table 1. Your Update query is somewhat unclear to me.

For instance, lets say I have the following textboxes:

txtCostA1 - holds the Cost where Type = A and Range = 1
txtCostA2 - holds the Cost where Type = A and Range = 2
txtCostB1 - holds the Cost where Type = B and Range = 1
txtCostB2 - holds the Cost where Type = B and Range = 2

txtPartsA1 - holds the Parts where Type = A and Range = 1
txtPartsA2 - holds the Parts where Type = A and Range = 2
txtPartsB1 - holds the Parts where Type = B and Range = 1
txtPartsB2 - holds the Parts where Type = B and Range = 2

I'm wondering how the query gets each textbox value into the right spot in Table 1 ?

 

[vbaInet]

Why are you having multiple instances of textboxes?

I thought your desired results layout is a continuous form or a datasheet?

 

[Deutz]

Yes, I want a datasheet layout for viewing but I thought you meant multiple textboxes for editing when you said "Here's one more suggestion, why not display the records and allow editing in separate boxes above the subform."

I suppose you actually meant one box to enter the Range, one to enter the Type, one to enter the Cost and one to enter the Parts?

Sorry if i'm a bit slow to catch on.

 

[vbaInet]

That's fine, I will (or attempt to) draw a visual:

   txtForeignKey|txtFieldNm|txtRange|txtA  |txtB     <-- textboxes for editting
###################################################
#  ForeignKey...|FieldNm...|Range...|A.....|B...  #
#  ---------------------------------------------  #
#  202..........|Cost......|1.......|$10...|$100  #
#  202..........|Cost......|2.......|$50...|5     #    <-- subform displaying the datasheet
#  202..........|Parts.....|1.......|120...|30    #
#  202..........|Parts.....|2.......|10....|0     #
###################################################

 

[Deutz]

Your Update query now makes perfect sense.

Thanks vbaInet for your help and patience.

 

[vbaInet]

You're welcome!

Just keep us informed of your progress.