Creating a query and adding columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a small table which I would like to base a query from.

columns
sales id, date, quantity

The quantity column will have a value of 1,2,or 3. i've set the perameters
to search for a specific sales id and date range but i would like to create
three new columns in the query to pull the total
number for each quantity value 1.2 and 3. This would be a one line summary
of counts for a specific sales id.

I would like the outcome to look something like this.

Sales Id, total records in quantity, total of 1's from quantity, total of
2's from quantity, total of 3's from quantity.

Thanks for the help!

BL
 
Create a Totals query with three calculated fields
sales id - GroupBy
quantity - Sum
qty1: [quantity] - Sum / Criteria quantity = 1
qty2: [quantity] - Sum / Criteria quantity = 2
qty3: [quantity] - Sum / Criteria quantity = 3
 
Thanks for the reply. I'm still having problems. Here's what I did.
In a new query I created a
Sales Id = Group By (in the Total: row)
quantity = Sum (in the Total: row)

New Columns:
Field: row I entered: qty_1: [quantity]
Total: row I entered: Sum
Criteria: row I entered: 1

No values are returning when I run the query.

Here's what I was hoping the results would look like.

Column 1: Sales Id ( one sales person )
Column 2: Quantity (this should equal the total number of entries in this
field for the person)

Column 3: The Quantity column will have a value of 1,2, or 3.
Column 3 should be the total number of "1" in Quantity
Column 4: Total number of "2" in quantity
Column 5: Total number of "3" in quantity

Thanks a lot for the help.

BL







Klatuu said:
Create a Totals query with three calculated fields
sales id - GroupBy
quantity - Sum
qty1: [quantity] - Sum / Criteria quantity = 1
qty2: [quantity] - Sum / Criteria quantity = 2
qty3: [quantity] - Sum / Criteria quantity = 3

relacy said:
Hi, I have a small table which I would like to base a query from.

columns
sales id, date, quantity

The quantity column will have a value of 1,2,or 3. i've set the perameters
to search for a specific sales id and date range but i would like to create
three new columns in the query to pull the total
number for each quantity value 1.2 and 3. This would be a one line summary
of counts for a specific sales id.

I would like the outcome to look something like this.

Sales Id, total records in quantity, total of 1's from quantity, total of
2's from quantity, total of 3's from quantity.

Thanks for the help!

BL
Click to expand...
Click to expand...
 
relacy said:
Hi, I have a small table which I would like to base a query from.

columns
sales id, date, quantity

The quantity column will have a value of 1,2,or 3. i've set the perameters
to search for a specific sales id and date range but i would like to create
three new columns in the query to pull the total
number for each quantity value 1.2 and 3. This would be a one line summary
of counts for a specific sales id.

I would like the outcome to look something like this.

Sales Id, total records in quantity, total of 1's from quantity, total of
2's from quantity, total of 3's from quantity.
Click to expand...

Try using the Crosstab Query wizard, using the quantity field as the column
header and the sales ID as the row header.
 
Thanks John that works great! One more question please. Is there a way to
add three additional columns to the crosstab to calculate the percentage for
values 1,2,and 3.
Like:
total of quantity 1 / total of quantity
total of quantity 2 / total of quantity
total of quantity 2 / total of quantity

Your time and assistance is much appreciated.

BL
 
Back
Top