SQL Question

[Guest]

I have a query that shows values for each date in a month, sorted by date.
True to form, however, SQL only retrieves dates that exist in the table. I
would like to show a null, or zero value for each date for which there is no
data. Is there a way to do this with a select query similar to using an "In
(" clause in a crosstab query to define column headings, regardless if data
exists for it?

Thanks in advance.

DEI

 

[Douglas J. Steele]

You need to have a table that has a row for each date, and then use a Left
Join (or Right Join: depends on how you're creating the query)

 

[Vincent Johns]

To illustrate what Douglas Steele suggested, suppose you have the
following data in your [Values] Table...

[Values] Table Datasheet View:

Date Name
---- ------
27 Last
1 One
3 Three

and you've created the following Table, as Douglas suggested:

[Days] Table Datasheet View:

DayNumber
---------
1
2
3
4
28
29
30
31

(Yes, I know some days are missing -- you'd need to include all 31.)

To account for months with differing numbers of days, you could use a
Query something like this one. The [Month] parameter, as it stands, you
would have to enter in a dialog box while running this Query, but in
real life I expect you'd probably calculate it somehow.

[Q_DateValues] SQL:

SELECT Days.DayNumber, Values.Name
FROM Days LEFT JOIN [Values]
ON Days.DayNumber = Values.Date
WHERE (((Days.DayNumber)
<=Day(DateSerial(Year(Now()),[Month]+1,1)-1)))
ORDER BY Days.DayNumber;

Assuming you enter 2 for the value of [Month], signifying February (of
2005, if you run it this week), you'd get the following output:

[Q_DateValues] Query Datasheet View:

DayNumber Name
--------- ------
1 One
2
3 Three
4
27 Last
28

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

 

[Guest]

Post you SQL statement and I will fill it out for you.


SELECT CVDate([Enter start date])+[Count] AS [My Dates]
FROM [Count]
WHERE (((CVDate([Enter start date])+[Count])<=CVDate([Enter end date])));

 

[Guest]

Thank you Douglas and Vincent, but I knew I could create another table and
join, etc., I just thought there might be an easier solution.

Karl - here is my SQL statement, again it skips dates because no data exists
for certain days. The first two fields show the day of the month and the
actual date, respectively.

Thanks for the help.

DEI

SELECT VOLUME.Day, VOLUME.Date, Sum(VOLUME.UW_Volume) AS Volume
FROM VOLUME
GROUP BY VOLUME.Day, VOLUME.Date, VOLUME.Dept
HAVING (((VOLUME.Dept)=10050470100));

Post you SQL statement and I will fill it out for you.


SELECT CVDate([Enter start date])+[Count] AS [My Dates]
FROM [Count]
WHERE (((CVDate([Enter start date])+[Count])<=CVDate([Enter end date])));

I have a query that shows values for each date in a month, sorted by date.
True to form, however, SQL only retrieves dates that exist in the table. I
would like to show a null, or zero value for each date for which there is no
data. Is there a way to do this with a select query similar to using an "In
(" clause in a crosstab query to define column headings, regardless if data
exists for it?

Thanks in advance.

DEI

 

[Guest]

Create a table named Count with numbers zero through 31. The query below
will generate a list of date for the current month. You can easily change to
list last months days.

SELECT Date()-Day(Date())+1+[count] AS Date_Volume, Date()-Day(Date())+1 AS
Start, DateAdd("m",1,Date()-Day(Date())+1)-1 AS [End]
FROM [Count]
WHERE (((Date()-Day(Date())+[count])<DateAdd("m",1,Date()-Day(Date())+1)-1))
GROUP BY Date()-Day(Date())+1+[count], Date()-Day(Date())+1,
DateAdd("m",1,Date()-Day(Date())+1)-1;

This query sums the volume for Dept 10050470100 for the previous month and
includes all days of the month.
SELECT Volume.Day, Volume_Dates.Date_Volume, Volume_Dates.Start,
Volume_Dates.End, Sum(IIf([Dept]="10050470100",[UW_Volume],0)) AS [Volume
Total], Volume.Dept
FROM Volume_Dates LEFT JOIN Volume ON Volume_Dates.Date_Volume = Volume.Date
GROUP BY Volume.Day, Volume_Dates.Date_Volume, Volume_Dates.Start,
Volume_Dates.End, Volume.Dept
ORDER BY Volume_Dates.Date_Volume;

Thank you Douglas and Vincent, but I knew I could create another table and
join, etc., I just thought there might be an easier solution.

Karl - here is my SQL statement, again it skips dates because no data exists
for certain days. The first two fields show the day of the month and the
actual date, respectively.

Thanks for the help.

DEI

SELECT VOLUME.Day, VOLUME.Date, Sum(VOLUME.UW_Volume) AS Volume
FROM VOLUME
GROUP BY VOLUME.Day, VOLUME.Date, VOLUME.Dept
HAVING (((VOLUME.Dept)=10050470100));

Post you SQL statement and I will fill it out for you.


SELECT CVDate([Enter start date])+[Count] AS [My Dates]
FROM [Count]
WHERE (((CVDate([Enter start date])+[Count])<=CVDate([Enter end date])));

I have a query that shows values for each date in a month, sorted by date.
True to form, however, SQL only retrieves dates that exist in the table. I
would like to show a null, or zero value for each date for which there is no
data. Is there a way to do this with a select query similar to using an "In
(" clause in a crosstab query to define column headings, regardless if data
exists for it?

Thanks in advance.

DEI