CrossTab query w/param

E

elena

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
 
D

Duane Hookom

Does your IssueDate field contain a time element also? Do your results vary
if you use:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
 
E

elena

Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Duane Hookom said:
Does your IssueDate field contain a time element also? Do your results vary
if you use:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
Click to expand...
Click to expand...
 
D

Duane Hookom

Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Duane Hookom said:
Does your IssueDate field contain a time element also? Do your results vary
if you use:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
Click to expand...
Click to expand...
Click to expand...
 
E

elena

Duane, thank you for reply,
I found the problem, it was tblDates
I populated this table from VB 6.0 from datetime picker control
dtPicker.Value property return date and time, in the table itself datatype of
the fields are short date.
Thank you, again now it return the same amount of tickets with different
param.

Duane Hookom said:
Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

Duane Hookom said:
Does your IssueDate field contain a time element also? Do your results vary
if you use:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

elena,
It's good to hear you got this sorted out. It seems you are still confusing
the "format" of a field with the actual value/precision of a field.
Formatting is generally for display only. Lots of Access developers think
that if they set the Format of a date/time field to "Short Date", it will
only store the date. This is not true. A date/time always stores the time
portion. The time might be midnight if the value is entered with only the
date part.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Duane, thank you for reply,
I found the problem, it was tblDates
I populated this table from VB 6.0 from datetime picker control
dtPicker.Value property return date and time, in the table itself datatype of
the fields are short date.
Thank you, again now it return the same amount of tickets with different
param.

Duane Hookom said:
Is the ActCode field ever Null? Is it really a text field? What happens if
you use:
PIVOT IIf([ActCode] & "" = "10","Valid","Void") IN ("Valid","Void");
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


elena said:
Thank you, Duane
No IssueDate field is short date, no time element, i tried with DateValue
and it returns the same result less then it should be,
What else can i try, i need those dates range for the report,
Please, advice

:

Does your IssueDate field contain a time element also? Do your results vary
if you use:
TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE DateValue(TempTicks.IssueDate) Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi, All
I have a query, all credits to Duane.
I build query from two tables, tblDates has only two fields, where i store
date range
start date and end date.
My problem is that this query doesn't return the same count of tickets, if i
change
date range: example
table has 51 records
date range 11/18/07 - 11/25/07 return 51 tickets (for 11/18 and 11/25 no
tickets at all)
date range 11/19/07 - 11/25/07 return 45 tickets(for 11/25 no tickets) .
table has the same amount of tickets, it is just date range changes.

Please, help how date range effects total count, if table has the same
amount of tickets?
How to fix it?

TRANSFORM Nz(COUNT(TempTicks.TicketNo),0) AS CountofTicks
SELECT [Shield] AS Badge, StartDate, EndDate, Sum(TempTicks.Amount) AS
SumOfAmount, Nz(COUNT(TempTicks.TicketNo),0) AS TotalTicks
FROM TempTicks, tblDates
WHERE TempTicks.IssueDate Between [StartDate] AND [EndDate]
GROUP BY [Shield], StartDate, EndDate
PIVOT IIf([ActCode]="10","Valid","Void") IN ("Valid","Void");
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

How to remove the prompt criteria from the report ? 4
Crosstab query for the report 2
Crosstab with multiple fields 2
Crosstab query Help with Totals 2
crosstab query with Sum -conditional 0
Crosstab query with multiple fields 2
Crosstab report with totals in the footer 2
crosstab with conditional SUM() 1

Top