By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Record for every date

P: 5
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
Nov 24 '06 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Nov 24 '06 #2

P: 5
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
Nov 24 '06 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 24 '06 #4

PEB
Expert 100+
P: 1,418
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!
Nov 26 '06 #5

P: 5
Hi All

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

Cheers
Mar 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.