Don't want criteria to be exclusive

[Guest]

My query is linked to a couple tables, one of which contains the fields Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

Now I can almost get this to work, except that then I set the criteria to
[Type]="IND", it does not include any rows where that condition isn't true.
Therefore, I set those criteria in all of the TypeTotal Columns, no rows
appear because no rows match all criteria, only one of them, at the most.

So, any ideas? Is there a better way to do this than set criteria? I can
provide more info if needed

 

[Duane Hookom]

I don't see any records with a Type of "IND". If you set the criteria to one
of the values of Type what would you expect to see?

 

[Guest]

I meant to say Type=I, I is short for IND, B is short for BUS, R is short for
RES. DOesn't really matter though. What I would like is for the query to
appear as it does below (that's not how it appears now. If I run the query,
no rows appear because no single row fulfills all the criteria. Each of those
totalling rows is just Nz([hours]) with respective criteria. I want these
columns to total certain rows so that I can sum the different types
seperately in a report. Ideas? Thanks forthe help.

I don't see any records with a Type of "IND". If you set the criteria to one
of the values of Type what would you expect to see?

--
Duane Hookom
MS Access MVP

My query is linked to a couple tables, one of which contains the fields
Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

Now I can almost get this to work, except that then I set the criteria to
[Type]="IND", it does not include any rows where that condition isn't
true.
Therefore, I set those criteria in all of the TypeTotal Columns, no rows
appear because no rows match all criteria, only one of them, at the most.

So, any ideas? Is there a better way to do this than set criteria? I can
provide more info if needed

 

[Guest]

Also, I can't use group by to do this because I need to sum these categories
vertically, not horizontally, and I need to group by date anyway.

I meant to say Type=I, I is short for IND, B is short for BUS, R is short for
RES. DOesn't really matter though. What I would like is for the query to
appear as it does below (that's not how it appears now. If I run the query,
no rows appear because no single row fulfills all the criteria. Each of those
totalling rows is just Nz([hours]) with respective criteria. I want these
columns to total certain rows so that I can sum the different types
seperately in a report. Ideas? Thanks forthe help.

I don't see any records with a Type of "IND". If you set the criteria to one
of the values of Type what would you expect to see?

--
Duane Hookom
MS Access MVP

My query is linked to a couple tables, one of which contains the fields
Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

Now I can almost get this to work, except that then I set the criteria to
[Type]="IND", it does not include any rows where that condition isn't
true.
Therefore, I set those criteria in all of the TypeTotal Columns, no rows
appear because no rows match all criteria, only one of them, at the most.

So, any ideas? Is there a better way to do this than set criteria? I can
provide more info if needed

 

[Guest]

Also, I can't use group by to do this because I need to sum these categories
vertically, not horizontally, and I need to group by date anyway.

I meant to say Type=I, I is short for IND, B is short for BUS, R is short for
RES. DOesn't really matter though. What I would like is for the query to
appear as it does below (that's not how it appears now. If I run the query,
no rows appear because no single row fulfills all the criteria. Each of those
totalling rows is just Nz([hours]) with respective criteria. I want these
columns to total certain rows so that I can sum the different types
seperately in a report. Ideas? Thanks forthe help.

I don't see any records with a Type of "IND". If you set the criteria to one
of the values of Type what would you expect to see?

--
Duane Hookom
MS Access MVP

My query is linked to a couple tables, one of which contains the fields
Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

Now I can almost get this to work, except that then I set the criteria to
[Type]="IND", it does not include any rows where that condition isn't
true.
Therefore, I set those criteria in all of the TypeTotal Columns, no rows
appear because no rows match all criteria, only one of them, at the most.

So, any ideas? Is there a better way to do this than set criteria? I can
provide more info if needed

 

[John Vinson]

My query is linked to a couple tables, one of which contains the fields Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

PMFJI - try creating three calculated fields:

TypeR: IIf([Type] = "R", [Hours], 0)
TypeI: IIf([Type] = "I", [Hours], 0)
TypeB: IIF([Type] = "B", [Hours], 0)

Do a Totals query summing these three fields (and summing [Hours]
itself if you want the grand total), with whatever Group By is
appropriate.

John W. Vinson[MVP]

 

[Guest]

Jon,
That's it! That was exactly what I was looking to do, thank you SO much!
- Evan

My query is linked to a couple tables, one of which contains the fields Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

PMFJI - try creating three calculated fields:

TypeR: IIf([Type] = "R", [Hours], 0)
TypeI: IIf([Type] = "I", [Hours], 0)
TypeB: IIF([Type] = "B", [Hours], 0)

Do a Totals query summing these three fields (and summing [Hours]
itself if you want the grand total), with whatever Group By is
appropriate.

John W. Vinson[MVP]

 

[Duane Hookom]

I think a crosstab might be more flexible with Date, Type, Sum of Hours as
row headings and "Type" & Type & "Total" as column heading and Sum of Hours
as the Value.

--
Duane Hookom
MS Access MVP

Jon,
That's it! That was exactly what I was looking to do, thank you SO much!
- Evan

My query is linked to a couple tables, one of which contains the fields
Date,
Type, Hours. There are only four different categories of "types."

In the query then created four additional columns. In these columns, I
wanted to total each "type" category seperately. I would want the query
to
look like this:

Date Type Hours TypeRTotal TypeITotal TypeBTotal
1/4 R 2 2
5/2 I 5 5
3/24 B 9
9
2/12 R 4 4

PMFJI - try creating three calculated fields:

TypeR: IIf([Type] = "R", [Hours], 0)
TypeI: IIf([Type] = "I", [Hours], 0)
TypeB: IIF([Type] = "B", [Hours], 0)

Do a Totals query summing these three fields (and summing [Hours]
itself if you want the grand total), with whatever Group By is
appropriate.

John W. Vinson[MVP]