Query to show all dates in parameter

[Pyro]

Hey,

I have a query that pulls data out between 2 dates which works as it should (showing entries and their corresponding dates), but is there a way to show every date in that range, sequentially, even if there is no change on those dates?

Thanks for looking.

 

[Galaxiom]

You want records in the output query that includes dates in the range that do not have records in the input tables?

Make a one sided join to a complete calendar table.

Order by tblCalendar.fieldDate

 

[Pyro]

Thanks Galaxiom, that put me on the right track and now i have a query that shows all the gaps in my data based on sequential dates. Which brings me to my next problem:

So the data that i have filtered shows lot numbers (lot) of products (product) listed on the date that each was put into use (start_date). I have changed the query to a crosstab query so that it displays the data as below:

---------Date1 Date2 Date3 Date4 ...
product1 lot1---------lot2--------
product2 lota---------------lotb
product3 -------------------loty
...

With gaps between the data as above.

What i want to do is fill the gaps with the most recent lot number that preceded it, so that it looks like this:

---------Date1 Date2 Date3 Date4 ...
product1 lot1---lot1---lot1--lot2
product2 lota---lota---lotb--lotb
product3 lotx---lotx---lotx---loty
...

I only need to view it like this, not be saved in any form.

Any thoughts are appreciated.

 

[Galaxiom]

Maybe you had better elucidate your ultimate goals at this point.
It is starting to look distinctly like there might be a better approach to the problem.

I am comforted at least by the fact you say you don't need to store this result.

 

[Pyro]

My goals are to view the information exactly as outlined in the last part of my post above. I'll probably put it onto a form in a locked datasheet or something.

My database stores the start dates that lot numbers of products are put into use. I want to view date by date through a range which batch numbers were in use regardless of whether nothing changes in a day. For the purposes of this, i want to assume that a lot is in use for a product until another lot is put in use, at which point the previous lot can be assumed finished.

The first column will show the product, the first row will show the sequential date through a range and the values will be populated with lot numbers.

Hope this is clearer.

 

[Galaxiom]

Would not this table appear like an overwhelming mass of dates?

Wouldn't it be better to have a report that sumarised the lot information you wanted for either a particular product or day? For example, enter the date and get a list of the current lots for all products on that day.

 

[Pyro]

It would look like a really ugly spreadsheet, which is what it is being modelled off.

I have already a report that shows what products were in use on a date or through a series of dates, along with several filtering options.

This is just another view of the same information that i have been asked to try to come up with. Believe me i think it's ridiculous too.

 

[Galaxiom]

OK. Some old hands can't change the way they think. Been in your position before.

I fear the solution is as hideous as the product but i will bet you don't much care for elegance at this point. Why use silk to make a sack?

Use an expression that finds the maximum date in the product field of the LotDate source table that is less than the current date drawn from the calendar table.

Something horrible like could go into the query but it is probably better to apply something similar but simpler to the crosstab query as an nz function.

IIF(ISNull([thefield], DMax("[LotDate]", "[tblLotDate]", "[tblLotDate].[LotDate]<=[tblCalendar].[fieldDate]"), whatever for not null)

I can't imagine this will be right but you get the idea.