Help with query...

[Emily]

Hi,

I have a query that tells me when I should start making a product and when
to finish making it. What I would like the query to do is to take the
resources needed in to account when determining the start time.

Right now I have it as a text box that comes up when you run the query and
it enters the time. Unfortunately it enters the time for all of the batches.

Below is what I have right now.

Start Time Finish Part 1
Resource
9/9/07 5:00 AM 0.30 9/9/07 5:17 AM 2
9/9/07 5:00 AM 1.49 9/9/07 6:29 AM 3
9/9/07 5:00 AM 1.93 9/9/07 6:55 AM 1
9/9/07 5:00 AM 2.08 9/9/07 7:04 AM 2
9/9/07 5:00 AM 1.49 9/9/07 6:29 AM 3
9/9/07 5:00 AM 0.74 9/9/07 5:44 AM 1
9/9/07 5:00 AM 0.74 9/9/07 5:44 AM 2
9/9/07 5:00 AM 0.74 9/9/07 5:44 AM 3
9/9/07 5:00 AM 1.49 9/9/07 6:29 AM 1


This is what I would like it to look like

Start Time Finish Part 1
Resource
9/9/07 5:00 AM 0.30 9/9/07 5:17 AM 2
9/9/07 5:00 AM 1.49 9/9/07 6:29 AM 3
9/9/07 5:00 AM 1.93 9/9/07 6:55 AM 1
9/9/07 5:17 AM 2.08 9/9/07 7:21 AM 2
9/9/07 6:29 AM 1.49 9/9/07 7:58 AM 3
9/9/07 6:55 AM 0.74 9/9/07 7:39 AM 1
9/9/07 7:21 AM 0.74 9/9/07 8:05 AM 2
9/9/07 7:58 AM 0.74 9/9/07 8:42 AM 3
9/9/07 7:39 AM 1.49 9/9/07 9:08 AM 1


So basically it is just taking the finish time from the previous entry and
putting it as the new entry's start time if the resources are equal.

I have a module that already calculates the finish time for me, but I need
to find a way to do the above stated problem.

Any help will be greatly appreciated!

Thank you,
Emily M.

 

[Lance]

How are your tables set up? Can you provide some sample records?

 

[Emily]

I have a table called "Schedule TABLE" that has all of the data concerning
the batches. I have a table called "tblPreBatchResources" that tells me,
based on what number priority a batch is, which resource to use:
Priority PB Resource ID
1 1
2 2
3 1
4 2
5 1

That is an example of the table.
The last table, called "prebatch lead times per bulk" tells me how long it
should take for the first process, called prebatch.

Bulk (a name for the product) PB LT (the time in decimal hours)
AAA123 1.5
ABC222 .74
EDF345 .83

Is this what you meant? If you need more, please let me know.

Thanks,
Emily

 

[Lance]

What does schedule table look like? How are you linking tblPreBatchResources
with prebatch lead times per bulk?

 

[Emily]

The Schedule table has the following fields that pertain to this query:
Priority (number 1-?)
Bulk
WorkOrderNumber
Status

prebatch lead times per bulk is linked by [bulk] to "schedule table"

tblprebatchresources is linked by [priority] to "schedule table"