Qeury with totals

[Kiko2323]

Hii Guys

As for school I need to work with Access and set up some queries.
I'm almost there but still miss just one little thing in my work, and I don't have that much experience with Access.

Here is my problem:

I have a table were I sell items

Items---------------Sales--------------% (I want this column in my query)
Item 1----------------2----------------22,22%
Item 2----------------5----------------55,55%
Item 3----------------2----------------22,22%

Total sold-------------9-----------------100%

I now want a second column next to the Sales column where I can see the percentages, example:
Item 1 sold 2, total of 9 --> 22,22%
Item 2 sold 5, total of 9 --> 55,55%
Item 3 sold 2, total of 9 --> 22,22%

How can I add up this table into my query?

 

[Pat Hartman]

You need to add a second query that summarizes the data. I can't tell how you are grouping this but maybe it is Order or Customer. The totals query will return a single line with the total for that order or customer or whatever your group is. Then you can join the original query to the new query on the group field or fields. That gives you the total of 9 and you can calculate the percentage by dividing the item count by the total.

 

[Kiko2323]

So...
I have made a query where I can see the amount of sales per item
its this:
Items---------------Sales
Item 1----------------2
Item 2----------------5
Item 3----------------2
Total sold-------------9

So now I have to make another query?
Can I not just make a table with the expression:[Sales]/[Total sold]?
Is it not possible to use that one record where the '9' is?

 

[Pat Hartman]

The total sold row is not in the query. You need two queries.
1. Select Something, Items, Sum(Sales) as ItemSales From YourTable
Group by Something, Items

2. Select Something, Sum(Sales) as TotalSales from YourTable
Group by Something

Now you have two queries. One with three rows (one for each item) and a second with one row representing the total. You haven't mentioned what "something" is. Perhaps you haven't gotten that far with the logic yet but you will NEED "something" to form a group.

Save the first two queries using meaningful (but short) names.

Create a third query. Rather than selecting tables, switch to the query tab and select the two original queries. Draw a join line between the two queries on the "something" field. Select all the columns from query1, select TotalSales from query2. Add a new calculated column that is named PctOfSales and the expression is:
ItemSales/TotalSales

Assuming that TotalSales can never be 0, this is all you need.