Record for every date

[Drivememad]

Hi

Not 100% how to do this but I am sure it can be done, so your help would be greatly appreciated.

For example, I have a crosstab qry to count admissions and group by day. At the moment if there isn't an admission on a particular day then the records may jump from 15/11/2006 to 17/11/2006. What I would like to do is have a continued list to show the 16th as well showing a zero count.

I have created another table of all the dates which I think I need to include but not link and I need to filter out on the criteria line. I would prefer to use this than SQL

Am I making sense and can anyone help
Cheers
Julie

[nico5038]

For this you need a so-called "outer join" query.
Place your table with dates and the other table in the graphical query editor.
Now drag the date from the tblDates to the other table and a line (the JOIN) will appear.
Double-click this line and you get a popup with 3 choices. Chose 2 or 3 making the tblDates "leading".
Now place the fields needed and save your query to get all values.

Clear ?

Nic;o)

[Drivememad]

Thanks Nic

I know what you mean, but I'm not sure that that is what I am looking for, so I'll have a sleep over the weekend and think about what I am really looking for and stop wasting everyones time

Cheers again Nic

[NeoPa]

FYI
Access will happily (assuming no errors found) convert queries between Design view and SQL view.
This means you can provide SQL easily when you've only 'Designed' the query, and also that you can try out SQL posted here (or elsewhere for that matter) by pasting into a Query's SQL view and then just select Design view to see it in ordinary Design format.

[PEB]

Here it is good to say that you can introduce only the days from 1 to 31 without introducing all dates in the year....

But in this case will appear 31 for april! or for november... that isn't usual! /simply doesn't arrives each year ;) for my short life experience - never, but one beauty day maybe there will be 31 of november/

So the best should be to introduce into a different table all months and days without years!

Just like:
1/1
1/2
1/31
2/01
2/28
2/29 /february is bad month do the same problem as the days in the months/

When you have this info in your query you process your date like this:
Mynewdate: datepart("m",[Mydate])+"/"+Day([Mydate])

You save the query

Create new one
Add the previous query and your table with the days. Do a join between the Mynewdate column and the column with the days in your table! Click on the join and specify: Display all records from the table and the equal records from the query /your query and your table names on the respective places/

And everything is ok! No more need of reflexion!

[Drivememad]

Hi All

Thanks for your help and responses, I have got this one sorted now

Cheers