multiple critera for one field in a query

N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.

Its hard to explain, but assume i have a table which consists of...

A 550
B 600
C 20
A 100
C 75
B 120
D 390

Then in a query I am trying to have the result of:
Over 500 Over 100 Over 25 Under 25
A 1 1
0 0
B 1 1
0 0
C 0 0
1 1
D 0 1
0 0

The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.

any help would be greatly appreciated.
 
J

Jeff Boyce

I'm having trouble visualizing the structure of your table ... and "how"
depends on "what".

That said, have you looked into Access HELP for "cross-tab queries"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
X

XPS350

Hi, i am trying to get a count of a certain criteria but in different
columns.

Its hard to explain, but assume i have a table which consists of...

A   550
B   600
C   20
A   100
C   75
B   120
D   390

Then in a query I am trying to have the result of:
         Over 500            Over 100      Over 25      Under 25
A           1                        1
0                 0
B           1                        1
0                 0
C           0                        0
1                 1
D           0                        1
0                 0

The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.

any help would be greatly appreciated.
Click to expand...

Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:

TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25")))


Groeten,

Peter
http://access.xps350.com
 
N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.
Click to expand...
Its hard to explain, but assume i have a table which consists of...
Click to expand...
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Click to expand...
Then in a query I am trying to have the result of:
         Over 500            Over 100      Over 25      Under 25
A           1                        1
0                 0
B           1                        1
0                 0
C           0                        0
1                 1
D           0                        1
0                 0
Click to expand...
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
Click to expand...
any help would be greatly appreciated.
Click to expand...

Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:

TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25")))

Groeten,

Peterhttp://access.xps350.com- Hide quoted text -

- Show quoted text -[/QUOTE]

Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.
Click to expand...
 
N

NPell

Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
Click to expand...
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) [QUOTE]
Groeten,[/QUOTE]

Peterhttp://access.xps350.com-Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.- Hide quoted text -

- Show quoted text -[/QUOTE]

I think i am getting there with the cross-tab query - can you help me
to refine it, i have:

TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000);

I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000

Can anyone help?

Thanks in advance.
Click to expand...
Click to expand...
 
N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.
Its hard to explain, but assume i have a table which consists of...
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Then in a query I am trying to have the result of:
         Over 500            Over 100     Over 25      Under 25
A           1                       1
0                 0
B           1                       1
0                 0
C           0                       0
1                 1
D           0                       1
0                 0
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
any help would be greatly appreciated.
Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) 
Groeten, 
Peterhttp://access.xps350.com-Hidequoted text - 
- Show quoted text -[/QUOTE][/QUOTE]
[QUOTE]
Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

I think i am getting there with the cross-tab query - can you help me
to refine it, i have:

TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000);

I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000

Can anyone help?

Thanks in advance.- Hide quoted text -

- Show quoted text -[/QUOTE]

Sorry, i used some of Peters (XPS350) suggestion, and came up with:

TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT IIf([Amount]>500,"Over 500",IIf([Amount]>100,"Over 100",IIf
([Amount]>25,"Over 25","Under 25")))

But, access is telling me "You tried to execute a query that does not
include the specified expression "LoggedDate" as part of an aggregate
function."
Click to expand...
Click to expand...
Click to expand...
 
N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.
Its hard to explain, but assume i have a table which consists of....
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Then in a query I am trying to have the result of:
         Over 500            Over 100      Over 25      Under 25
A           1                        1
0                 0
B           1                        1
0                 0
C           0                        0
1                 1
D           0                        1
0                 0
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
any help would be greatly appreciated.
Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) 
Groeten, 
Peterhttp://access.xps350.com-Hidequotedtext - 
- Show quoted text - 
Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.- Hide quoted text - 
- Show quoted text -[/QUOTE][/QUOTE]
[QUOTE]
I think i am getting there with the cross-tab query - can you help me
to refine it, i have:[/QUOTE]
[QUOTE]
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000);[/QUOTE]
[QUOTE]
I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000[/QUOTE]
[QUOTE]
Can anyone help?[/QUOTE]
[QUOTE]
Thanks in advance.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

Sorry, i used some of Peters (XPS350) suggestion, and came up with:

TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT IIf([Amount]>500,"Over 500",IIf([Amount]>100,"Over 100",IIf
([Amount]>25,"Over 25","Under 25")))

But, access is telling me "You tried to execute a query that does not
include the specified expression "LoggedDate" as part of an aggregate
function."- Hide quoted text -

- Show quoted text -[/QUOTE]

I realise that im just spamming this - unintentionally - but i have
the solution.
Using the help provided here, and just trial and error :

TRANSFORM Count(tblData.Amount) AS CountOfAmount
SELECT Format([tblData].[LoggedDate],"mmm yy") AS [Month]
FROM tblData
GROUP BY Format([tblData].[LoggedDate],"mmm yy"), Year([LoggedDate]),
Month([LoggedDate])
ORDER BY Year([LoggedDate]), Month([LoggedDate])
PIVOT IIf([Amount] >50000,"Over £50000",IIf([Amount]>10000,"Over
£10000",IIf([Amount]>2500,"Over £2500","Under £2500")));

Thanks everyone :)
Click to expand...
Click to expand...
Click to expand...
 
X

XPS350

Hi, i am trying to get a count of a certain criteria but in different
columns.
Its hard to explain, but assume i have a table which consists of....
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Then in a query I am trying to have the result of:
         Over 500            Over 100      Over 25      Under 25
A           1                        1
0                 0
B           1                        1
0                 0
C           0                        0
1                 1
D           0                        1
0                 0
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
any help would be greatly appreciated.
Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) 
Groeten, 
Peterhttp://access.xps350.com-Hidequotedtext - 
- Show quoted text - 
Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.- Hide quoted text - 
- Show quoted text -[/QUOTE][/QUOTE]
[QUOTE]
I think i am getting there with the cross-tab query - can you help me
to refine it, i have:[/QUOTE]
[QUOTE]
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000);[/QUOTE]
[QUOTE]
I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000[/QUOTE]
[QUOTE]
Can anyone help?[/QUOTE]
[QUOTE]
Thanks in advance.- Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

Sorry, i used some of Peters (XPS350) suggestion, and came up with:

TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT IIf([Amount]>500,"Over 500",IIf([Amount]>100,"Over 100",IIf
([Amount]>25,"Over 25","Under 25")))

But, access is telling me "You tried to execute a query that does not
include the specified expression "LoggedDate" as part of an aggregate
function."[/QUOTE]

Please post the structure of your tabel and tell us what you want
referring to fieldnames of that table.

Note that in your first post it looked like you wanted to count
records. Now you have a sum in your query.


Groeten,

Peter
http://access.xps350.com
Click to expand...
Click to expand...
Click to expand...
 
N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.
Its hard to explain, but assume i have a table which consists of...
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Then in a query I am trying to have the result of:
         Over 500            Over 100     Over 25      Under 25
A           1                       1
0                 0
B           1                       1
0                 0
C           0                       0
1                 1
D           0                       1
0                 0
The way i have done it was do 4 queries for that criteria, thentry
and do a count from those queries.
im just getting myself in a mess.
any help would be greatly appreciated.
Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) 
Groeten, 
Peterhttp://access.xps350.com-Hidequotedtext- 
- Show quoted text - 
Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.- Hide quoted text - 
- Show quoted text - 
I think i am getting there with the cross-tab query - can you help me
to refine it, i have: 
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000); 
I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000 
Can anyone help? 
Thanks in advance.- Hide quoted text - 
- Show quoted text -[/QUOTE][/QUOTE]
[QUOTE]
Sorry, i used some of Peters (XPS350) suggestion, and came up with:[/QUOTE]
[QUOTE]
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT IIf([Amount]>500,"Over 500",IIf([Amount]>100,"Over 100",IIf
([Amount]>25,"Over 25","Under 25")))[/QUOTE]
[QUOTE]
But, access is telling me "You tried to execute a query that does not
include the specified expression "LoggedDate" as part of an aggregate
function."[/QUOTE]

Please post the structure of your tabel and tell us what you want
referring to fieldnames of that table.

Note that in your first post it looked like you wanted to count
records. Now you have a sum in your query.

Groeten,

Peterhttp://access.xps350.com- Hide quoted text -

- Show quoted text -[/QUOTE]

Yeah i was working with an example from the internet too (so it was
broken down and i could understand it, as its my first cross-tab
query).
Your code really helped though.

I was wondering, if the criteria is not met and all entries result to
a count of zero - the column does not show, is there anyway to make it
show regardless?
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
N

NPell

Hi, i am trying to get a count of a certain criteria but in different
columns.
Its hard to explain, but assume i have a table which consistsof...
A   550
B   600
C   20
A   100
C   75
B   120
D   390
Then in a query I am trying to have the result of:
         Over 500            Over 100      Over 25      Under 25
A           1                        1
0                 0
B           1                        1
0                 0
C           0                        0
1                 1
D           0                        1
0                 0
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
any help would be greatly appreciated.
Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:
TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25"))) 
Groeten, 
Peterhttp://access.xps350.com-Hidequotedtext- 
- Show quoted text - 
Thanks, i will look into cross-tab queries and get back to you ifi
get stuck again.
Appreciate it guys, cheers.- Hide quoted text - 
- Show quoted text - 
I think i am getting there with the cross-tab query - can you help me
to refine it, i have: 
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT ([tblData].[Amount] In (>=50000,between 49999 and
10000,<100000); 
I am trying to group the amounts, by month into
- Above £50,000
- Between £10,000 and £49,999
- Less than £10,000 
Can anyone help? 
Thanks in advance.- Hide quoted text - 
- Show quoted text - 
Sorry, i used some of Peters (XPS350) suggestion, and came up with: 
TRANSFORM Sum([tblData].[Amount]) AS SumOfAmount
SELECT [tblData].[LoggedDate]
FROM tblData
GROUP BY format([tblData].[LoggedDate],"mmm yy")
PIVOT IIf([Amount]>500,"Over 500",IIf([Amount]>100,"Over 100",IIf
([Amount]>25,"Over 25","Under 25"))) 
But, access is telling me "You tried to execute a query that does not
include the specified expression "LoggedDate" as part of an aggregate
function."[/QUOTE][/QUOTE]
[QUOTE]
Please post the structure of your tabel and tell us what you want
referring to fieldnames of that table.[/QUOTE]
[QUOTE]
Note that in your first post it looked like you wanted to count
records. Now you have a sum in your query. 

Peterhttp://access.xps350.com-Hide quoted text -[/QUOTE]
[QUOTE]
- Show quoted text -[/QUOTE]

Yeah i was working with an example from the internet too (so it was
broken down and i could understand it, as its my first cross-tab
query).
Your code really helped though.

I was wondering, if the criteria is not met and all entries result to
a count of zero - the column does not show, is there anyway to make it
show regardless?
Thanks in advance.- Hide quoted text -

- Show quoted text -[/QUOTE]

Found out how... (For anyone that searches this forum)...
Design View > Properties > Column Headings:
"Over £50000","Over £10000","Over £2500","Under £2500"
Match them up to the headings in the SQL query.

Thanks again to Peter and Jeff
Click to expand...
Click to expand...
Click to expand...
 
Z

Zac Thompson

Hi, i am trying to get a count of a certain criteria but in different
columns.
Click to expand...
Its hard to explain, but assume i have a table which consists of...
Click to expand...
A 550
B 600
C 20
A 100
C 75
B 120
D 390
Click to expand...
Then in a query I am trying to have the result of:
Over 500 Over 100 Over 25 Under 25
A 1 1
0 0
B 1 1
0 0
C 0 0
1 1
D 0 1
0 0
Click to expand...
The way i have done it was do 4 queries for that criteria, then try
and do a count from those queries.
im just getting myself in a mess.
Click to expand...
any help would be greatly appreciated.
Click to expand...

Indeed a cross-tab query would be a good solution. If the fields in
your table are named 'code' and 'number', it looks like:

TRANSFORM Nz(Count([Number]),0) AS NumberOfNumber
SELECT
Code: 
FROM YourTable
GROUP BY [Code]
PIVOT IIf([Number]>500,"Over 500",IIf([Number]>100,"Over 100",IIf
([Number]>25,"Over 25","Under 25")))

Groeten,

Peterhttp://access.xps350.com- Hide quoted text -

- Show quoted text -[/QUOTE]

Thanks, i will look into cross-tab queries and get back to you if i
get stuck again.
Appreciate it guys, cheers.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting values over multiple records in one Query using Loop statement 1
AND on 2 fields along with other criteria in query 2
Query Help Please 2
calculating a total amount 7
Missing Row Field in Crosstab Query 1
5 Fields Multiple Values 5
Query is too complex for Access 2007 ? 2
count three values in multiple fields 2

Top