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='Nothing')
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.Sembilan'
Or State='Pahang'
Or State='Terengganu'
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.Sembilan'
Or State='Pahang'
Or State='Terengganu'
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.

[Stewart Ross]

Hi. You should store your states in a separate State table. You can then include a column in that table to store the custom sort order, as a numeric value (an integer or long number). You can then define the sort order you want by setting values in ascending order. For example, Perlis would have sort order 1, Perak 2 and so on. You would subsequently sort on the custom sort order field, not alphabetically on the state name.

You could also include a status field for each state showing whether or not the states are active.

If you store these details in a base table you can do away with the hard-coding in your query. The approach you are taking at present is very difficult to maintain. Consider what happens if you add a new state; at present you have to change your query to accommodate the new state, whereas if you put all relevant details in your state table your query will not have to change at all.

...
WHERE 
((State='Perlis' 
Or State='Kedah' 
Or State='Perak' 
Or State='P.Pinang' 
Or State='Kuala Lumpur' 
Or State='Selangor' 
Or State='Melaka' 
...
 

-Stewart

[akmaRudiliyn]

Ok, but actually i cannot modify the structure of my table.
How about if i have column name Race with various of values.
From the value i want to group by it into Foreign, Bumiputera and Non Bumiputera.

Eg:
Foreign
Korean
Japan

Bumiputera
Malay

Non Bumiputera
Chinese
Indian

[akmaRudiliyn]

Hai..anyone can help me..
I need to make report use database access.
I use sql query that given by friends here.
But i have face 2 problem.

a) How to filter data,to make data in database, either with gap (space) and without gap,as same data.

Eg:
1) Pulau Pinang same data as PulauPinang
2) Pulau Pinang same as P.Pinang
3) __(Gap) Pulau Pinang = Pulau Pinang

b) how to make data is upper case same with lower case
Eg: Pulau Pinang = PULAU PINANG
pulau pinang = PULAU PINANG

i had found this function to make it al uppercase but not work. Mybe i code in wrong way.
--UCASE(c)

Help me pliz..

[Stewart Ross]

HI akma. You really need to post a separate thread for a new question.

UCase is very simple to use and does indeed convert from mixed case to all uppercase. Like any function, if you want to use it in a report without including it in your report's recordsource you will need to place an unbound control on the report, then in the control's controlsource property set it to

=UCase([your field name])

You must make sure that the control is not named the same as the field you are trying to convert to uppercase (or the same as any other field in your report's recordsource) as this will cause an error.

My preference would be to include such computed fields in the underlying query on which the report is based, then the computed field can just be dragged and dropped onto the report in design view as needed.

There is no easy answer to your part (a) question; it is not possible in one operation to cover all possible variations in spelling of place names that might be encountered and replace them with one standard version when you have not designed tables for selection of standard places and place names, as originally recommended to you in answer to your earlier post.

It is possible to remove spaces from a string - the Replace function will do this for you - but if you cannot use UCase at present I suspect you will not be in a position to use Replace either.

If you want to try it as a computed field in a query, you can remove spaces like this:

NewComputedField = Replace([your place name field], " ", "")

-Stewart

[akmaRudiliyn]

Thank you for your help Stewart.