sum query to append to table (1 Viewer)

[Summer123]

Hi,
Can someone please help on summing up 2 rows and posting the values in a table?

So here i what i have
ImpID P1Hrs P2Hrs P3Hrs P4Hrs
DBD 34 60 56 16
DBC 21 20 38 6

I want to add so the result is
DBD/DBC 55 80 94 22

then post the value above in a main table under P1Tothrs, P2Tothrs, P3Tothrs, P4Tothrs

I've tried the Sum function and it works but i dont know how to get it to append to the main table... I've also tried DSum("P1Hrs","TblImp","ImpId = 'DBD' AND 'DBC'") as an Expression in the query but it returns 34 and not 55

please help!!!!!!!!! Urghhhhh

thanks!
Summer

 

[plog]

Wow, that's got to be a record for number of poor database design flags in a single post. Let me get the admonishments out of the way first, then I'll give you code to do exactly what you asked to do.

First, when you enumerate field names (i.e. P1Hrs, P2Hrs, etc.) you probably have an improperly structured database. You should most likely break these fields off and into a seperate table calld Hrs, where it holds hours, an id back to the main table and the HrTypes (i.e. 1, 2, etc.)

Second, if DBD and DBC fields are related (which they are because you want to sum them together), you need to let the database know that. I'm just guessing here, but I bet the first 2 characters ('DB') mean something and the last character ('D' and 'C') mean somelthing as well. If so, they should be in seperate fields.

Third, rarely is it appropriate to store a calculated value. You probably shouldn't sum the fields up and then post it to a table. You should probably just sum them up in the query and use that query when you need the calculation.

As promised, here's the code to do what you want:

INSERT INTO TableToAppendTo ( ImpId, P1Hrs, P2Hrs, P3Hrs, P4Hrs )
SELECT "DBD/DBC" AS Idimp, Sum(YourTableNameHere.P1Hrs) AS SumOfP1Hrs, Sum(YourTableNameHere.P2Hrs) AS SumOfP2Hrs, Sum(YourTableNameHere.P3Hrs) AS SumOfP3Hrs, Sum(YourTableNameHere.P4Hrs) AS SumOfP4Hrs
FROM YourTableNameHere
WHERE (((YourTableNameHere.ImpId)="DBD" Or (YourTableNameHere.ImpId)="DBC"))
GROUP BY "DBD/DBC";

Replace YourTableNameHere with the data source that currently contains the data. Replace TableToAppendTo with the name of the table the data needs to go to.

 

[Summer123]

hello plog thank you for your reply. I still dont get the sum of hours appended, it appends 2 rows on the main table. And DBD and DBC are not related but the user can pick one of the 2 or both to get total hours. Hope that helps. thanks again. If anyone has any other suggestions please let me know.

 

[plog]

What are the 2 rows of data it is appending? Please show the actual data.

 

[Summer123]

nevermind i had some statements wrong... i apologize! ok so now it WORKS...which is great but I have changed it to select query as my code would not work on append query but it doesnt post the total in the text box... gives me #Name in the text box its in the SubFrm... i'll attach the DB..

in the end i want to store those hours calculated in the Subfrm in the main form under each name.. so if you look at the main form it has 2 records, John and Mike. John will have DBD hours where Mike will have DBD and DBC hours...

How do i do this? again in have not done this before so you're right its prob a bad design but i need to learn. Please help. thank you.

 

[plog]

You've got a long way to go before you start worrying about forms. Read up on normalization (http://support.microsoft.com/kb/100139). You also need to store your data as the proper datatype. You are storing numbers as text, which when you try and do mathematical operations on them behave unexpectedly (i.e. "170" + "14" = "17014")

And like I mentioned in my first post, the data you are trying to store shouldn't be stored but calculated as needed.