calculations in queries

[Jessica]

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I am
trying to do with my database. I want to be able to add a field together
only if two other fields match.


TIA,
Jess

 

[KARL DEWEY]

My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not Match")

 

[Jessica]

Thanks for the reply Karl but I wanted to add records together. This is my
table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.

My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not Match")

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I am
trying to do with my database. I want to be able to add a field together
only if two other fields match.


TIA,
Jess

 

[Douglas J. Steele]

SELECT [Date], Brand, Flavor, Sum([Number Of Cases]) AS TotalCases
FROM MyTable
GROUP BY [Date], Brand, Flavor

Note that Date is not a good choice for a field name: it's a reserved word,
and can lead to problems. I'd recommend renaming that field if you can. If
you absolutely cannot rename the field, make sure you enclose it in square
brackets, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply Karl but I wanted to add records together. This is my
table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.

My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not Match")

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I
am
trying to do with my database. I want to be able to add a field together
only if two other fields match.


TIA,
Jess

 

[Jessica]

Thank you Doug I renamed the Date field Production Date. Would I be able to
use the same query if I wanted to group by Production Date and Shift to get
a total amount of cases per shift?

Thanks again,
Jess

SELECT [Date], Brand, Flavor, Sum([Number Of Cases]) AS TotalCases
FROM MyTable
GROUP BY [Date], Brand, Flavor

Note that Date is not a good choice for a field name: it's a reserved
word, and can lead to problems. I'd recommend renaming that field if you
can. If you absolutely cannot rename the field, make sure you enclose it
in square brackets, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply Karl but I wanted to add records together. This is
my table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.

My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not
Match")

:

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I
am
trying to do with my database. I want to be able to add a field
together
only if two other fields match.


TIA,
Jess

 

[Douglas J Steele]

Just ensure that any non-aggregate field you add to the SELECT portion is
added to the GROUP BY portion as well. (In other words, any field that
doesn't have a function like SUM, AVG, COUNT, etc.)

SELECT [Production Date], Brand, Flavor, Shift, Sum([Number Of Cases]) AS
TotalCases
FROM MyTable
GROUP BY [Production Date], Brand, Flavor, Shift

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug I renamed the Date field Production Date. Would I be able to
use the same query if I wanted to group by Production Date and Shift to get
a total amount of cases per shift?

Thanks again,
Jess

SELECT [Date], Brand, Flavor, Sum([Number Of Cases]) AS TotalCases
FROM MyTable
GROUP BY [Date], Brand, Flavor

Note that Date is not a good choice for a field name: it's a reserved
word, and can lead to problems. I'd recommend renaming that field if you
can. If you absolutely cannot rename the field, make sure you enclose it
in square brackets, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply Karl but I wanted to add records together. This is
my table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.



My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not
Match")

:

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I
am
trying to do with my database. I want to be able to add a field
together
only if two other fields match.


TIA,
Jess

 

[Jessica]

Thanks Doug I was playing around with this query at work today and am
somewhat getting an understanding of what group by does. I guess my question
is I have a query grouped by Production Date, Brand, and Flavor which gives
me a total number of cases of each brand and flavor produced on that date.
Now if I also wanted the total number of cases for the day produced by shift
regardless of brand and flavor I would have to build another query and group
by Production Date and shift Correct?

Thanks again,
Jess

Just ensure that any non-aggregate field you add to the SELECT portion is
added to the GROUP BY portion as well. (In other words, any field that
doesn't have a function like SUM, AVG, COUNT, etc.)

SELECT [Production Date], Brand, Flavor, Shift, Sum([Number Of Cases]) AS
TotalCases
FROM MyTable
GROUP BY [Production Date], Brand, Flavor, Shift

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug I renamed the Date field Production Date. Would I be able to
use the same query if I wanted to group by Production Date and Shift to get
a total amount of cases per shift?

Thanks again,
Jess

SELECT [Date], Brand, Flavor, Sum([Number Of Cases]) AS TotalCases
FROM MyTable
GROUP BY [Date], Brand, Flavor

Note that Date is not a good choice for a field name: it's a reserved
word, and can lead to problems. I'd recommend renaming that field if
you
can. If you absolutely cannot rename the field, make sure you enclose
it
in square brackets, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks for the reply Karl but I wanted to add records together. This
is
my table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.



My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not
Match")

:

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of what I
am
trying to do with my database. I want to be able to add a field
together
only if two other fields match.


TIA,
Jess

 

[Douglas J Steele]

Yup.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug I was playing around with this query at work today and am
somewhat getting an understanding of what group by does. I guess my question
is I have a query grouped by Production Date, Brand, and Flavor which gives
me a total number of cases of each brand and flavor produced on that date.
Now if I also wanted the total number of cases for the day produced by shift
regardless of brand and flavor I would have to build another query and group
by Production Date and shift Correct?

Thanks again,
Jess

Just ensure that any non-aggregate field you add to the SELECT portion is
added to the GROUP BY portion as well. (In other words, any field that
doesn't have a function like SUM, AVG, COUNT, etc.)

SELECT [Production Date], Brand, Flavor, Shift, Sum([Number Of Cases]) AS
TotalCases
FROM MyTable
GROUP BY [Production Date], Brand, Flavor, Shift

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug I renamed the Date field Production Date. Would I be

able
to

use the same query if I wanted to group by Production Date and Shift to get
a total amount of cases per shift?

Thanks again,
Jess

SELECT [Date], Brand, Flavor, Sum([Number Of Cases]) AS TotalCases
FROM MyTable
GROUP BY [Date], Brand, Flavor

Note that Date is not a good choice for a field name: it's a reserved
word, and can lead to problems. I'd recommend renaming that field if
you
can. If you absolutely cannot rename the field, make sure you enclose
it
in square brackets, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks for the reply Karl but I wanted to add records together. This
is
my table

Date Brand Flavor Number of Cases Shift

10/01/05 Barilla Marinara 3000 A1
10/02/05 Newmans Plain 1000 B1
10/19/05 Barilla Marinara 1000 B2
10/20/05 Barilla Marinara 2000 A1
10/20/05 Barilla Marinara 500 B1


If the date, brand , and flavor match then add the number of cases. So
10/20/05 would be 2500.



My Display Column: Iif([Field1]=[Field2], [Field3]+[field4], "Not
Match")

:

I forgot to ask. Does anyone know of any access template that have
calculations in their queries that I can kind of get an idea of

what
I

am
trying to do with my database. I want to be able to add a field
together
only if two other fields match.


TIA,
Jess

 

[aaron.kempf]

yeah.. the best way to give a real reporting platform-- with
flexibility to change granularities without comign back to a db
person-- is to use OLAP-- Analysis Services with office web components.

these are the most powerful pivotTables in the world.