create missing records

[RobTuby]

Hi

First post on this forums! be gentle!

I have a table called Imported that contains rows of data with calendar entries.
What i need is a query that would add in the missing dates With the details field been marked #Free o it would have concurrent dates

I have uploaded a sample of the database containing a sample from the table

Thanks

Rob
View attachment missing dates.accdb

 

[plog]

You can't do that with just a query. Probably going to need some VBA to generate those records to a table. Not exactly sure what the end game is, but you can't just query data up where there isn't any in the underlying source.

 

[RobTuby]

I need the full calendar for reporting purposes. I cannot change the source data or produce utilisation rates.

 

[plog]

Then you would need a table with the full calendar you intend to report on.

 

[RobTuby]

The recordset is generated from Mycalendar and excel of which i cannot change the coding it uses to create the template or records. so will only create an entry if data is entered into it.

 

[CJ_London]

so as plog says, you will need a table of dates

tblCalendar
calDate
01/01/2014
02/01/2014
....
....

and to add the missing dates into your other table use a left join

INSERT INTO myTable (EventDate) SELECT calDate
FROM tblCalendar LEFT JOIN myTable of tblCalendar.calDate=myTable.EventDate
WHERE myTable.EventDate is Null

To populate tblCalendar you can either write some VBA or use Excel - put your start date in cell A1 then in A2 put =A1+1 then copy down as far as you need. The copy and paste the result into the table