Problem in running or open the query

[akmaRudiliyn]

Hi Everybody..

This is my firstime in creating report using microsoft access.
Actually i need to build a report based on a database call Table1.
I only use 2 column of databse to put in my report.

1) State
2) Status

For status, i need to separate it based on value of status;
Eg: 1)Ready
2) Not ready

I had create 2 query,one for ready and one for not ready.
I combine this 2 query into one query called StateStatus to build the report.

When i run or open to view the StateStatus query,the error message occur.I only can view it by design view.

Error:
The query can not be completed either the size of the query result is larger than the maximum size of databse (2GB) or there is not enough temprary storage space on the disk to store the query result.


Is it my step to create query for report is wrong?
How to solve to make the query can run or open.


Thank you.

[Stewart Ross]

Hi. It sounds like you have accidentally multiplied your two queries when you tried to join them back to each other. This is known as the Cartesian product of the rows, and happens when you include the tables but neglect to join them on the relevant common field. I'm not sure what you want to do with the two fields you mention - Status and State - but the most obvious thing is to group by the values concerned and count the rows for each state.

You don't need two queries to do this. Simplest approach is to use the Access query editor, add your primary table to the query, use View, Totals to select grouping and totalling, add the state and status fields, then add a copy of the status field, giving it a different name (Count of Status, say) and change its Group By to Count.

-Stewart

[nico5038]

It would be easy for us when you would post a sample you need like:

State Ready NotReady
UK      123       33
DK       22        5
etc...
 

This can be done by a groupby query or a crosstable query.

Nic;o)

[akmaRudiliyn]

Thank you Stewart Ross Inverness and nico5038 for your help.

Yes, actually i want to have the same structure that had stated by nico5038,
but i dont know how to do.
Actually Status have 3 value.

1)Ready
2)Not Ready
3)Nothing

I already done the query but it is not work. It only show for Status Ready,
but Status Not Ready and Nothing are not showed. Maybe my query is wrong.

My query:

SELECT State, Count(Status) AS Status1
FROM Table1
WHERE (Status='Ready' )
GROUP BY State

UNION

SELECT State, Count(Status) AS Status2
FROM Table1
WHERE (Status='Not Ready')
GROUP BY State

UNION

SELECT State, Count(Status) AS Status3
FROM Table1
WHERE (Status='Nothin g')
GROUP BY State;

Anyone can help me?

[Stewart Ross]

As Nico said, you can do this as a group by or crosstab query. There is no need for the union-query approach. The group-by version, which shows counts for each Status on separate rows, is:

SELECT State, Count(Status) AS StatusCount FROM Table1 GROUP BY State, Status;

The Crosstab version, which shows all status values as column headings, is:

TRANSFORM Count([Status]) As StatusCount
SELECT State From Table1
GROUP BY State
PIVOT Status;

-Stewart

[nico5038]

Or the ultimate "GroupBy does it all in one query" solution:

SELECT State
, Sum(IIF(Status='Ready',1,0)) AS Ready
, Sum(IIF(Status='Not Ready',1,0)) AS NotReady
, Sum(IIF(Status='Nothing',1,0)) AS Nothing
, Sum(IIF(IsNull(Status),1,0)) AS Empty
FROM Table1
GROUP BY State
 

Nic;o)

[akmaRudiliyn]

Thank you again to Stewart Ross Inverness and nico5038 for help me.

Now my problem is solved. Thank you so much :).I want to ask another question.
Below is my query same as nico5038 give before. I just use it because look simple.

My query:

SELECT Language,
Sum(IIf(State=' UK',1,0)) AS State1,
Sum(IIf(State=' EGYPT',1,0)) AS State2,
Sum(IIf(State=' RUSIA',1,0)) AS State3,
Sum(IIf(State=' MEXICO',1,0)) AS State4,
Count([No TG]) As TGAmount,
--abcdefg--
FROM Student
GROUP BY Language;


At --abcdefg-- space,
actually i want to make a query that can count all the value of [No TG] group by State.
how can i combine the query?

(SELECT Count([No TG]) AS (TotalTG) From [Maklumat Pelajar] GROUP BY State)

Once again..anyone can help me? :)

[nico5038]

Just switch the Language field by the [No TG] field and see the effect.

Nic;o)

[akmaRudiliyn]

Thank you all for helping me.

1) I had sort my data in ascending order A-Z,but it is not sort properly. Why it is happen? How to solve it?

Eg:

C
B
A
A,B
A,B,
B


2) I also want to know the right way to filter use Visual Studio .net. Let say i want to filter the column <Blank> and Brunei for State row.

Eg:

State
<Blank>
Brunei
Singapore
Rusia

[akmaRudiliyn]

Below is my code to filter data. I want to arrange the data by state from north to south. First, Perlis, 2nd Perak and so on until Sabah. But the data is still sort by a-z(ascending) or z-a(descending) order. For example, Johor, Kedah,Kelantan. .Terengganu..I dont want this order.

Anyone can check my query below..

SELECT State,
Sum(IIf(Status= 'Ready',1,0)) AS Ready,
Sum(IIf(Status= 'Not Ready Aktif',1,0)) AS NotReady,
Sum(IIf(Status= 'Cancel',1,0)) AS Cancel,
Sum(IIf(Status= 'Nothing',1,0)) AS Nothing,
Sum(IIf(IsNull( Status),1,0)) AS Empty,
Count(*) AS Total
FROM Table1

WHERE
((State='Perlis '
Or State='Kedah'
Or State='Perak'
Or State='P.Pinang '
Or State='Kuala Lumpur'
Or State='Selangor '
Or State='Melaka'
Or State='N.Sembil an'
Or State='Pahang'
Or State='Terengga nu'
Or State='Kelantan '
Or State='Johor'
Or State='Sabah'
Or State='Sarawak' ))

GROUP BY Negeri

ORDER BY
((State='Perlis '
Or State='Kedah'
Or State='Perak'
Or State='P.Pinang '
Or State='Kuala Lumpur'
Or State='Selangor '
Or State='Melaka'
Or State='N.Sembil an'
Or State='Pahang'
Or State='Terengga nu'
Or State='Kelantan '
Or State='Johor'
Or State='Sabah'
Or State='Sarawak' ));

Anyway, this code is not work. It still sort data by ascending or descending, not follow as i arrange. Anyone can help me?Maybe my query is wrong.