Schedule Query

[NSAMSA]

Hi:

I have, what I think will be, a rather complicated question. Hopefully its simpler than I anticipate.

I'm developing a database in which the user will order materials based on a preset schedule. e.g. A product may run from 03/15/17 to 04/03/17, (15/03/17 for the European users). There are two issues. First, the schedule is built elsewhere and it has the start date for an item. However, the end date simply is when the next item has a start date. e.g. 8019976 will have 03/15/17 as its start date and then the next line will be the start date for 8021167 on 04/03/17. I would like to link to this table so that these dates do not need to be input. However, I would also like the query I run to understand that the start date on the next line will be the end date for the previous line. Is there an offset function I can use to make this understood, or some other work around?

Secondly, materials for the separate items may be ordered on a weekly basis. The goal is to keep ordering for materials down. So, if I have an item starting on 03/22/17 and going until 04/08/17 and I'm ordering for the week of the 19th through the 26th, I want the query to give me the needed materials inventory for this item from the 22nd to the 26th, (5 days). Pretty much, if a schedule is between 3/22/17 and 4/8/17, I'd like it to understand that this includes 3/25/17. If I do a between query, can I have it encompass all days for the item since it will be running on those days?

I'm not sure if any of this is doable, but would greatly appreciate any help I can get.

Thank you

 

[sneuberg]

I'm developing a database in which the user will order materials based on a preset schedule. e.g. A product may run from 03/15/17 to 04/03/17, (15/03/17 for the European users). There are two issues. First, the schedule is built elsewhere and it has the start date for an item. However, the end date simply is when the next item has a start date. e.g. 8019976 will have 03/15/17 as its start date and then the next line will be the start date for 8021167 on 04/03/17. I would like to link to this table so that these dates do not need to be input. However, I would also like the query I run to understand that the start date on the next line will be the end date for the previous line. Is there an offset function I can use to make this understood, or some other work around?

When a value is needed in one record that's in another some like to use a subquery like Allen Browne describes in the section Get the value in another record
in this Web page. I find it easier to use DMax as shown in the query below.

SELECT DMax("[EndDate]","[TableName]","[EndDate] < #" & [EndDate] & "#") AS StartDate, TableName.EndDate
FROM TableName;

The DMax gets the largest (latest) of all the other dates in the table that are less than itself. Note that the first record this query produces doesn't have a start date. It can't as there is no end date prior to the earliest end date. You could fill this in from some other field with the IIF or NZ function.

I'll look at the other part of your post later.

 

[sneuberg]

Secondly, materials for the separate items may be ordered on a weekly basis. The goal is to keep ordering for materials down. So, if I have an item starting on 03/22/17 and going until 04/08/17 and I'm ordering for the week of the 19th through the 26th, I want the query to give me the needed materials inventory for this item from the 22nd to the 26th, (5 days). Pretty much, if a schedule is between 3/22/17 and 4/8/17, I'd like it to understand that this includes 3/25/17. If I do a between query, can I have it encompass all days for the item since it will be running on those days?

If you want a query to return records where one date is between two other dates then the Between And Operator will work for you. But since your question involves four dates you may be looking for overlapping time periods. If that's the case then I suggest looking at PBaldy's explanation on how to set up a query for that.