Access Sum in Query

[Starboxes]

I want to create a new table with a query that will sum one field entry if 4
field entries all match to generate one entry in the new table from query
(make-table query).

The four fields that need to match are date, site, product, unit and the
field to sum is called adjustment. When I make a query that will group by
the 4 fields that need to match, then sum the adjustment field, i get results
that are multiples of the value i want.

For instance, the original entries may be:
1: Date: B Site: D Product: F Unit: I Amount: 26
2: Date: B Site: D Product: F Unit: I Amount: 569
3: Date: B Site: D Product: F Unit: I Amount: 5631
4: Date: A Site: C Product: E Unit: G Amount: 5463
5: Date: A Site: C Product: E Unit: H Amount: 632
6: Date: B Site: C Product: E Unit: G Amount: 698

The query result is:
1: Date: A Site: C Product: E Unit: G Amount: 27315 (which is 5463
times 5 from entry 4)
2: Date: A Site: C Product: E Unit: H Amount: 3160 (which is 632 times
5 from entry 5)
3: Date: B Site: D Product: F Unit: I Amount: 31130 (which is 6226
times 5 from entry 1, 2, and 3)
4: Date: B Site: C Product: E Unit: G Amount: 3490 (which is 698 times
5 from entry 6)

Am I trying to do something that can't be done?

 

[KARL DEWEY]

Post the SQL of your query.

 

[Starboxes]

Unfortunately, i have ZERO programming skills. so i may take this as a i
need to learn/fumble around with SQL some to do this...i'm trying to make a
huge spreadsheet i've been maintaining for over a year into a database
because i need to add many more sites and am out of columns on my
spreadsheet. Our IT department does not support Access (it's just a tool we
have available to use if we already know/learn) thus i cannot get any help
from them. Ideally i could get them to make a SQL database for me, but with
the economy like it is, we do not have funding available right now to fund
people to work on it for me, so i'm stuck attempting to do something myself.

 

[John Spencer]

It is rather simple to post the SQL of the query.
Open the query in design view
Select VIEW: SQL from the menu
Copy the text and paste it into your posting.

With that, someone can probably diagnose your problem and propose a
solution.

Right now, it is impossible to tell which of several "errors" could be
causing your problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

i need to add many more sites and am out of columns
Sounds like you may have your table structure wrong.

To post your query SQL open it in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

Analyzing your SQL will tell if your table structure is wrong and maybe how
to resolve your query sum problem.

 

[Bob Barrows]

Don't be so intimidated. Switch your query to SQL View (View menu,
right-click menu, or toolbar button) and copy the sql statement to the
clipboard.
See? No programming skills required ...

I'm not clear why a new table is needed. A better plan is to create a
query to generate this resultset whenever it is needed. That way you
won't face the problem of fixing the calculation results in a separate
table if the data in the source table changes. (there are situations
where storing calculated data is needed - you have not provided enough
information to let us help you make that decision).

I do have a question. Why is each total multiplied by 5?

I guess it doesn't matter.

With your query in SQL View, paste this statement in, fix your table
name and switch to Design View to see how you were supposed to create
this in Design View ("Date" is a horrible name for a field as it is a
"reserved keyword", which means it has a meaning to the database engine
and can cause problems if you are not careful - you should change it to
something like EntryDate, or whatever describes what it is the date of):

SELECT EntryDate, Site, Product, Unit,
5 * Sum([adjustment]) As AggregateAdjustment
FROM YourTable
GROUP BY EntryDate, Site, Product, Unit

This should give you what you want.

 

[Bob Barrows]

Oh wait ... I think I've misinterpreted your post. It would have made it
simpler if you had posted the rows of data that you WANTED your query to
return after showing us what it was returning.

So my sql statement is probably wrong. All you need to do to fix it is
delete that "5 * " bit ...

Don't be so intimidated. Switch your query to SQL View (View menu,
right-click menu, or toolbar button) and copy the sql statement to the
clipboard.
See? No programming skills required ...

I'm not clear why a new table is needed. A better plan is to create a
query to generate this resultset whenever it is needed. That way you
won't face the problem of fixing the calculation results in a separate
table if the data in the source table changes. (there are situations
where storing calculated data is needed - you have not provided enough
information to let us help you make that decision).

I do have a question. Why is each total multiplied by 5?

I guess it doesn't matter.

With your query in SQL View, paste this statement in, fix your table
name and switch to Design View to see how you were supposed to create
this in Design View ("Date" is a horrible name for a field as it is a
"reserved keyword", which means it has a meaning to the database
engine and can cause problems if you are not careful - you should
change it to something like EntryDate, or whatever describes what it
is the date of):

SELECT EntryDate, Site, Product, Unit,
5 * Sum([adjustment]) As AggregateAdjustment
FROM YourTable
GROUP BY EntryDate, Site, Product, Unit

This should give you what you want.

Unfortunately, i have ZERO programming skills. .

 

[Starboxes]

Here's what i get when i view SQL. I don't want the data multiplied by 5,
but it seems to be doing that on its own...

SELECT [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier],
Sum([Adjustment Entry].[Adjustment Amount]) AS [SumOfAdjustment Amount]
FROM [Total Adjustment] INNER JOIN [Adjustment Entry] ON ([Total
Adjustment].[Report Date] = [Adjustment Entry].[Report Date]) AND ([Total
Adjustment].Site = [Adjustment Entry].Site) AND ([Total Adjustment].Product =
[Adjustment Entry].Product) AND ([Total Adjustment].[Unit Identifier] =
[Adjustment Entry].[Unit Identifier]), [Total Loss] INNER JOIN [Loss Entry]
ON ([Total Loss].[Report Date] = [Loss Entry].[Report Date]) AND ([Total
Loss].Site = [Loss Entry].Site) AND ([Total Loss].Product = [Loss
Entry].Product) AND ([Total Loss].[Unit Identifier] = [Loss Entry].[Unit
Identifier])
GROUP BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier]
ORDER BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier];

 

[Starboxes]

the adding more sites and being out of columns is an excel problem, hence the
reason for forcing me to learn some access capability (beyond contact lists
like i did in high school 10 years ago which is the last time i opened
access). i am very proficient in excel and access frustrates me because i
can't seem to make it do what i want and i don't know anyone that knows
anything about access (except my little brother who told me to run before
ever attempting, but i have no other options at work, i don't get much say in
the programs we have to use and getting something new would probably take
about a year...)

 

[Starboxes]

i have stumbled upon it working correctly by deleting the other tables i had
in the first version of the query to get this:

SELECT [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier],
Sum([Adjustment Entry].[Adjustment Amount]) AS [SumOfAdjustment Amount]
FROM [Adjustment Entry]
GROUP BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier]
ORDER BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier];

the only problem is i had wanted it to sum a losses entry table field as
well with the same 4 equal fields, but having that table in the query seems
to have caused the problem. i think i will have to modify the basic
structure of my database to get what i want how i want it unless someone else
has a better solution...

 

[Bob Barrows]

Without knowing the details of your database structure, I think the
reason for your results is that you have a one-to-many relationship with
the other table so you were getting the inflated numbers. What you can
do to get the information from the other tables is
1. Save this query
2. Create a new query joining that other table to this query.

i have stumbled upon it working correctly by deleting the other
tables i had in the first version of the query to get this:

SELECT [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier],
Sum([Adjustment Entry].[Adjustment Amount]) AS [SumOfAdjustment
Amount]
FROM [Adjustment Entry]
GROUP BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier]
ORDER BY [Adjustment Entry].[Report Date], [Adjustment Entry].Site,
[Adjustment Entry].Product, [Adjustment Entry].[Unit Identifier];

the only problem is i had wanted it to sum a losses entry table field
as well with the same 4 equal fields, but having that table in the
query seems to have caused the problem. i think i will have to
modify the basic structure of my database to get what i want how i
want it unless someone else has a better solution...

I want to create a new table with a query that will sum one field
entry if 4 field entries all match to generate one entry in the new
table from query (make-table query).

The four fields that need to match are date, site, product, unit and
the field to sum is called adjustment. When I make a query that
will group by the 4 fields that need to match, then sum the
adjustment field, i get results that are multiples of the value i
want.

For instance, the original entries may be:
1: Date: B Site: D Product: F Unit: I Amount: 26
2: Date: B Site: D Product: F Unit: I Amount: 569
3: Date: B Site: D Product: F Unit: I Amount: 5631
4: Date: A Site: C Product: E Unit: G Amount: 5463
5: Date: A Site: C Product: E Unit: H Amount: 632
6: Date: B Site: C Product: E Unit: G Amount: 698

The query result is:
1: Date: A Site: C Product: E Unit: G Amount: 27315 (which is
5463 times 5 from entry 4)
2: Date: A Site: C Product: E Unit: H Amount: 3160 (which is
632 times 5 from entry 5)
3: Date: B Site: D Product: F Unit: I Amount: 31130 (which is
6226 times 5 from entry 1, 2, and 3)
4: Date: B Site: C Product: E Unit: G Amount: 3490 (which is
698 times 5 from entry 6)

Am I trying to do something that can't be done?

 

[iinliuh luihnph]

no