which day in a date range doesn't have records?

hey guys, what's the more efficient way to find which date in a date range a table doesn't have records?

the context is - i'm building an attendance table.

so i've got a start date in a table, and my end date is today.

so staff will have to fill in the work hours for each day.

sometimes they will miss out saturdays or sundays or thursday for example. i want to make sure EVERY DAY IS FILLED IN.... MUAHAHAHAHA!!! so draconion isn't it!!!!! hahaha = PP


Question) what is the best way of doing it?

preference 1: i preferably want to use SQL
preference 2: i preferably don't want to have a table that i maintain calendar days or numbers in it for this to work.
preference 3: i would like to use super sexy SQL code that proves to myself that SQL is super duper powerful. cause i want to think in data sets.

i can use VBA no problems at all but it's very....  p r o c e d u r a a aa..aaaaa.aaa. l. oops! procedural programme malfunction!! haha = ))

thanks guys!! = )))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<i want to make sure EVERY DAY IS FILLED IN>
What if they legitimately miss a day? (out sick, family emergency, ...etc)
0
Jeffrey CoachmanMIS LiasonCommented:
Beyond that, you did not state the exact results you want
0
Jeffrey CoachmanMIS LiasonCommented:
,...On the one hand you could put each or the 7 days in the "Columns" (Which may, or may not be a good idea)...Then set the required property of each day field to Yes.

But I am confused as to how an attendance system would be sure that the End date will always be the current date?
(time my span midnight?)

So you may have to explain a bit more about this system...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

developingprogrammerAuthor Commented:
hi boag2000! thanks for looking into this!! = ))

1) they cannot miss a day. if they're sick, then they must fill in that day as sick leave when they come back

2) hrmm i'm not sure about the 7 days in the columns. if i denormalise it and set required to yes, what effect does it have? not sure what this does but i don't think the denormalising route is reaching my objective here = )

3) you are absolutely right! my system doesn't take into account spanning midnight haha = ) if someone works from monday to tuesday 1am, and then works on tuesday but forgets to log his time on tuesday, yes the system will not be able to catch it. i've got default records filled in but if somehow the default records get deleted, there is no checks that can verify that - so that is a planned system weakness = ) cause i've got no time to do a consummate one!! haha 0.0000001% i'm accepting here! cause it'll probably never happen haha = )

================

k so basically maybe let's take the technical approach.

i have a start date and an end date.

i have some records in the start and end date range.

how do i get each date that doesn't have a record? these no-record dates should appear in a row of their own.

e.g. 1 jan start date 10 jan end date

4,5,6 jan has records.

i need a query or some way (preferably SQL) that will return me records for 1,2,3,7,8,9,10 jan. 7 records it needs to return me in total.
0
Jeffrey CoachmanMIS LiasonCommented:
Then can you post a sample of your data...
Is this a one table system or a One to many?

If Jan 4,5,6 exist, then they are records...?
0
developingprogrammerAuthor Commented:
yes sir i can! = )

so in here, the staff table has the start date which is 1 sep.

the worktime table has the dates where staff has worked. 4,5,6 sep

however they forgot to fill in for 1,2,3,7,8,9,10 sep.

i want to write a query that will give me 1,2,3,7,8,9,10 sep because these dates are absent in the range which start from 1 sep till today (10 sep)

hrmm, increasingly i think it's very easy to do this using VBA cause if my app carries on for 5 years, how many records must it loop? 365*5 = 2000 records!

and a computer does that very very fast!

P.S. i know my multiplication needs a lot of improvement!! haha = ))
Database1--2-.zip
0
Jeffrey CoachmanMIS LiasonCommented:
Then to me you would need to store all the dates in a table.
(You could probably figure this out with a function or a loop, but to me, a table is easier to deal with...)

Presuming a table listing all dates (tblDates)
...with a primary key field named "Dates", that holds all of the dates in order

The query would be something like this:

SELECT WorkTime.RowPK, WorkTime.WorkDate, WorkTime.starttime, WorkTime.endtime, tblDates.Dates
FROM WorkTime RIGHT JOIN tblDates ON WorkTime.WorkDate = tblDates.Dates
WHERE (((WorkTime.RowPK) Is Null));
0
developingprogrammerAuthor Commented:
Yup yup fully agree boag2000, but if I store all the dates in the table, I can only store a static amount of dates. Eg I put in 5 or 50 years of data and that's it. Of course this is just coming from a purist point of view haha, but also from learning = ) I think the only way to add data to the table is to latch on to a function that people would run every day and loop through the last date in the table and the current date today and add those dates in the table.

Hrmm,I think I always have a very funny way of thinking haha. And I think it always get me into trouble!! Haha = )) but thanks so much boag2000!! = ))))
0
developingprogrammerAuthor Commented:
Oh but just a sideline topic,you know this weekend I read from SQL in the wild their mini series on outer joins and is null versus no exists and also comparing to not in. It was SERIOUSLY cool haha and really inspired me so so much to want to get into a proper fully fledged database server!! And also become super familiar with execution paths!! = )))
0
Jeffrey CoachmanMIS LiasonCommented:
....ok
0
developingprogrammerAuthor Commented:
Hrmm I never understood what dots mean. Haha = )
0
Gustav BrockCIOCommented:
Here is an article and code about exactly this:

Find and Generate Missing Values in an Access Table
http://www.devx.com/dbzone/Article/40345

It is "spot on" as JimD requires ...

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
......OK
;-)
0
developingprogrammerAuthor Commented:
fantastic code you wrote gustav!!!! that is elegant code! i would have done it successfully myself in VBA but gosh it would be ugly haha = )

i promise once i'm done with my project i'll read

How To Convince Your Parents You Can Care for a Pet Bunny (Robbie Readers) by Susan Sales Harkins, William H. Harkins.

hahaha such a cute book!!!! = ))
0
developingprogrammerAuthor Commented:
gustav i also love how you grouped the dimensioning of object variables together and the dimensioning of non-object variables together. it makes it so much easier to Set xxx = Nothing to clean up later on. i'm going to adopt this practice from you too!! = ))
0
Gustav BrockCIOCommented:
Great. And Susan loves fans. Have fun!

/gustav
0
developingprogrammerAuthor Commented:
Thanks gustav!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.