Please Help... KRISH

G

Guest

Hi! everyone,
Here is my SQL for crosstab query.

TRANSFORM Sum(Facility.[No of Beds]) AS [SumOfNo of Beds]
SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;

I want to include all dates (DT.dt) and sum in my query should result '0'
instead of NULL. How can I do that. Please help.
Note the DT.dt is a field in DT table which cannot be related to other tables.
Thanks
KRISH
 
J

John Spencer

Use the NZ function to return 0 if the result is NULL. If you use NZ Access
has a habit in SQL statements of turning the result into a string, so you
may need to wrap this in one of the conversion functions to force it to we a
number. I did in the example.

I would be very careful about using DT.dt to pivot on. If the table has
very many records your query will fail because it will hit the 255 column
limit. At a minimum, you might want to apply some date range criteria
against DT.dt


TRANSFORM CDbl(NZ(Sum(Facility.[No of Beds]),0)) AS [SumOfNo of Beds]

SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;
 
G

Guest

Hi John,
Thanks a lot. one of my prob is solved. Could you please give some idea on
the other one, which i'm explaining as below:

My DT.dt is storing all days in a month like 1-1-2006, 1-2-2006, .....
1-31-2006. Now I want am generating monthly report of beds booked. In my
report some days are ignored on which no booking is made. But I want to show
0 (zero) on that day also. How to solve the problem. Please Help.
Thanks.
KRISH

John Spencer said:
Use the NZ function to return 0 if the result is NULL. If you use NZ Access
has a habit in SQL statements of turning the result into a string, so you
may need to wrap this in one of the conversion functions to force it to we a
number. I did in the example.

I would be very careful about using DT.dt to pivot on. If the table has
very many records your query will fail because it will hit the 255 column
limit. At a minimum, you might want to apply some date range criteria
against DT.dt


TRANSFORM CDbl(NZ(Sum(Facility.[No of Beds]),0)) AS [SumOfNo of Beds]

SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;


KRISH said:
Hi! everyone,
Here is my SQL for crosstab query.

TRANSFORM Sum(Facility.[No of Beds]) AS [SumOfNo of Beds]
SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;

I want to include all dates (DT.dt) and sum in my query should result '0'
instead of NULL. How can I do that. Please help.
Note the DT.dt is a field in DT table which cannot be related to other
tables.
Thanks
KRISH
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I don't see a solution for that one. Perhaps someone else can help
you.


KRISH said:
Hi John,
Thanks a lot. one of my prob is solved. Could you please give some idea on
the other one, which i'm explaining as below:

My DT.dt is storing all days in a month like 1-1-2006, 1-2-2006, .....
1-31-2006. Now I want am generating monthly report of beds booked. In my
report some days are ignored on which no booking is made. But I want to
show
0 (zero) on that day also. How to solve the problem. Please Help.
Thanks.
KRISH

John Spencer said:
Use the NZ function to return 0 if the result is NULL. If you use NZ
Access
has a habit in SQL statements of turning the result into a string, so you
may need to wrap this in one of the conversion functions to force it to
we a
number. I did in the example.

I would be very careful about using DT.dt to pivot on. If the table has
very many records your query will fail because it will hit the 255 column
limit. At a minimum, you might want to apply some date range criteria
against DT.dt


TRANSFORM CDbl(NZ(Sum(Facility.[No of Beds]),0)) AS [SumOfNo of Beds]

SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;


KRISH said:
Hi! everyone,
Here is my SQL for crosstab query.

TRANSFORM Sum(Facility.[No of Beds]) AS [SumOfNo of Beds]
SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;

I want to include all dates (DT.dt) and sum in my query should result
'0'
instead of NULL. How can I do that. Please help.
Note the DT.dt is a field in DT table which cannot be related to other
tables.
Thanks
KRISH
Click to expand...
Click to expand...
Click to expand...
 
V

Vincent Johns

I would write another Query, taking its data from the Crosstab Query,
and specify formats for the fields for which you want to see zero.
(This is INSTEAD OF using the Nz() function, which would return a
string, which you couldn't format as a number.)

In Query Design View, right-click on the field's column, choose
Properties, and set the Format property to something like

0.00;-0.00;0.00;0.00

The four parts are the formats for positive, negative, zero, and null
values respectively.

Well, OK, you don't want fractional beds. So maybe your format would
look more like this:

0;-0;0;0

Also, if a "Decimal Places" property is shown, you'd want to set that to
0 (= no decimal fractions).

Nz() will also work, but the values won't line up the way you might want
them to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


John said:
Sorry, I don't see a solution for that one. Perhaps someone else can help
you.


Hi John,
Thanks a lot. one of my prob is solved. Could you please give some idea on
the other one, which i'm explaining as below:

My DT.dt is storing all days in a month like 1-1-2006, 1-2-2006, .....
1-31-2006. Now I want am generating monthly report of beds booked. In my
report some days are ignored on which no booking is made. But I want to
show
0 (zero) on that day also. How to solve the problem. Please Help.
Thanks.
KRISH

:

Use the NZ function to return 0 if the result is NULL. If you use NZ
Access
has a habit in SQL statements of turning the result into a string, so you
may need to wrap this in one of the conversion functions to force it to
we a
number. I did in the example.

I would be very careful about using DT.dt to pivot on. If the table has
very many records your query will fail because it will hit the 255 column
limit. At a minimum, you might want to apply some date range criteria
against DT.dt


TRANSFORM CDbl(NZ(Sum(Facility.[No of Beds]),0)) AS [SumOfNo of Beds]

SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;



Hi! everyone,
Here is my SQL for crosstab query.

TRANSFORM Sum(Facility.[No of Beds]) AS [SumOfNo of Beds]
SELECT GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
FROM DT, GuestHouse INNER JOIN Facility ON GuestHouse.ID = Facility.ID
WHERE (((Facility.[Facility Requested])="beds") AND
((Facility.dtFrom)<=[dt]) AND ((Facility.dtTo)>=[dt]))
GROUP BY GuestHouse.ID, GuestHouse.FullName, GuestHouse.Designation,
GuestHouse.DeptOrOrg, GuestHouse.Hall
PIVOT DT.dt;

I want to include all dates (DT.dt) and sum in my query should result
'0'
instead of NULL. How can I do that. Please help.
Note the DT.dt is a field in DT table which cannot be related to other
tables.
Thanks
KRISH
Click to expand...
Click to expand...
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

Please help... KRISH 7
Prob... please Help.... KRISH 1
Riddle me this you wizards 2
help me please 2
adding one more column to corss tab 1
Crosstab Query Fields 2
Itereate cross tab query over another query 0
Subquery Issue 1 5

Top