Add values from two diff record sources

[oihjk]

The layout of the tables are as such:
_______________________
Code of Accounts table:
Proj#/Var#/CodeofAccount/ManPower/Hours/Dollars

Variance table:
Proj#/Var#/MaterialCost/EquipCost/ConstructCost/StaffCost (+ Many others not related to this issue)
________________________

The Variance table is the main table. I've got a multi primary key with Proj# and Var#. I've put the Code of Accounts in a different table because, for every variance record there can be multiple code of account records.

What I'm wanting to do is add the sum of the Dollars from Code of Accounts table to the Sum of MaterialCost + EquipCost + ConstructCost + StaffCost in the Variance table and show it in a field(called Totals) on a form. I would like to do this in a query.

--What I've tried already was this: A totals query(QryCOA) of CodeofAccounts table which sums all the Dollars fields for each Proj# & Var# into a field called COATotals. I'm only showing Proj# (Group By)/ Var# (Group By)/ COATotals: Dollars (Sum).

--Then I created a query(QryVariance) with all fields from Variance Table then using the zoom I add MaterialCost + EquipCost + ConstructCost + StaffCost into field called VARTotals. The into Totals field I add COATotals + VARTotals. I'm showing Variance.* / VARTotals (MaterialCost + EquipCost + ConstructCost + StaffCost) / Totals (VARTotals + COATotals)

This gets the correct values with no problem. The issue I have is that it won't allow me to edit any of the fields in the query. I removed the QryCOA from the select and it allows me edit the records. It is probably because QryCOA is a Totals query summing the Dollars that I can't edit the records.

The end result I'm wanting:
I have a Variance Form and a Subform CodeofAccounts. I'm wanting to show the Totals field on the Variance form. I will also use the Totals field on reports and such. This is why I would like it to be in a query.

Any suggestion?

Thanks,
Eric

 

[oihjk]

I created a subtotal of Dollars on the "CodeofAccounts" subform called "COATotals" and put it in the Footer.

When trying to reference this field from the "Variance" subform using

 =Forms!CodeofAccounts.COATotal

I get #Name in the text box.

In case I wasn't clear in the previous post the forms are structured like this.

Main form: AFENumber
Subform of AFENumber: VarianceLogInput Subform
Subform of VarianceLogInput Subform: CodeofAccounts

Any suggestions appreciated thanks.

 

[BLeslie88]

From what i see...

Your primary key in both tables are the same... Or their names of the fields are.

"The Variance table is the main table. I've got a multi primary key with Proj# and Var#. I've put the Code of Accounts in a different table because, for every variance record there can be multiple code of account records."

This doesn't seem right to me I haven't put much thought into it, but I am sure someone else can better explain to me, or you can better explain why your table it set up this way.

Code of Accounts table:
Proj#/Var#/CodeofAccount/ManPower/Hours/Dollars

Variance table:
Proj#/Var#/MaterialCost/EquipCost/ConstructCost/StaffCost (+ Many others not related to this issue)

There just seems to be something ambiguous here... I am a bit tired from the holidays to write out a better table structure without better understanding the 2 tables...

 

[oihjk]

I did it this way so that I can have more than one Code of Account entered against each variance. ~edit~I do believe you are correct though. I should have used the PK from the CoA table as FK in the Variance table. ~edit~

So that the Variance Table Structure won't look like this:
_________________________________
Proj#/Var#/MaterialCost/EquipCost/ConstructCost/StaffCost/
CodeofAccount1/ManPower1/Hours1/Dollars1/
CodeofAccount2/ManPower2/Hours2/Dollars2/
CodeofAccount3/ManPower3/Hours3/Dollars3/
CodeofAccount4/ManPower4/Hours4/Dollars4
_________________________________

The reason I have the multi PK is so each proj# can have a var# starting at 0001 and count up, but it won't allow the same proj# to have 0001 twice. Therefore each record is identified by both fields.

 

[BLeslie88]

you got my gist.

There is nothing wrong with a dbl pk.

I just agree you should have used a fk for the code. Then this would all be clearer...

 

[Pat Hartman]

Variance table is not normalized. It contains a repeating group. Notice how each name in the group is suffixed by "Cost". That means that they are all costs of a different type and should be stored as a separate table. You would then have a "lookup" table that contained the names of the various costs. I'm also guessing that there might be some grouping associated with the costs that you use to get subtotals. That grouping code would be included in the lookup table. Once you normalize the costs, you will no longer need to create long calculations, you will simply use a totals query to sum selected rows.

Are you certain that the variance is related to multiple accounts? Wouldn't it be the individual costs that were related to an account?

FYI, using special characters or embedded spaces in your field names is poor form.

I also use multi-field primary keys but ONLY at the lowest level. If I have dependent tables, I use a unique index on my mult-field identifier and add an autonumber as the pk to make joins easier.

 

[oihjk]

The real issue

Pat and Brian, Thanks for replying to my post. I did take your advice and changed the PK to VarianceID instead of a multi PK. Joins are much easier this way. I've also changed all of the relationships in the database that referenced this, accordingly. In reference to the field naming scheme, I was just typing in short hand for this post.

As far as the "Cost" fields being in a lookup table, that wouldn't work. At least what I "think" you are suggesting won't.

This is the scenario, I will receive a Variance Sheet. It will contain an AFE or Project Number. The database will assign an incrementing variance number per project to each variance, so each Project variance number starts at 0001 and counts up. Among other data to be captured, the issue at hand is this:

On each Variance Sheet there is the option to reference multiple code of accounts (only a number referring to what kind of work was performed for each variance, from now on I'll call it "COA"). So if there was piping work + structural steel work it will require 2 COA's and so on. On a variance each COA has it's own Manpower and Hours worked, which are multiplied to get Manhours, it also has a dollar value. Fairly simple...

The Construction Service, Staff, Material and Equipment costs will only occur once per variance. The Mat. and Equip. are manually entered. The Construct. cost is to be (25% of the sum of the Manhours x $35) AND the Staff cost is to be (20% of sum of the Manhours x $35).

Finally add Construction + Staff + Material + Equipment + SumCOA Value to get a total variance cost.

I realize it's a bit wordy, but I hope this will clear things up.
Thanks for any assistance in this matter.

Eric