Optimization of resources dependent on sequence

[JStan89]

Hello all,

I've asked a question on here before regarding the same database I'm creating. I've ran into another snag...

Right now, I have an entire job with multiple piece parts. All of these different parts go through different processes (They are shown in the table "Piece Parts New"). I've created (was assisted on here how to do it) a query that the user puts in the EC (estimated completion) days per process of a given piece part, it automatically outputs a ESD (Estimated Start Date) and ECD (Estimated Completion Date) on all processes based on date the part was started.

So, now I want it to output a report that each piece part will:

1. Follow Sequence by Step #
2. Do longest part first (make number one priority at a given machine).
3. Only 1 part per machine at a time (The part that takes the longest to do will always be first, followed by the next longest, etc.)

Right now, the report labeled "Step Groupings" somewhat does this, however another part will have ESD a day later even though the part before still hasn't finished according to ECD.

This is an optimization problem, but it's over my head. Any help would be much appreciated!

Thanks!

 

[jdraw]

Based on your post here and reference to help received, I looked at your previous post(s) and the response by JHB. The table in your database in post#1 above is not normalized based on your relationships window.
It seems the sample provided in JHB's post had different structure.
What has changed?

It also seems you have not followed plog's advice, but I don't know your set up and certainly have not followed the thread. I'm just looking at help you have received based on your posts.

One thing I would stress is to adopt a naming convention that uses only alphanumeric and "_" underscore. No embedded spaces or special characters, it will save you a lot of syntax errors/frustration.

Perhaps you can tell us exactly the status of your database and development, and how your structure and approach uses the helpful info you said you received from AWF.

Good luck with your project.


After the original post:
Edit: I have looked again at your database, and am not sure what exactly you have. Definitely the relationships window doesn't reflect your database.

 

[JStan89]

Based on your post here and reference to help received, I looked at your previous post(s) and the response by JHB. The table in your database in post#1 above is not normalized based on your relationships window.
It seems the sample provided in JHB's post had different structure.
What has changed?

It also seems you have not followed plog's advice, but I don't know your set up and certainly have not followed the thread. I'm just looking at help you have received based on your posts.

One thing I would stress is to adopt a naming convention that uses only alphanumeric and "_" underscore. No embedded spaces or special characters, it will save you a lot of syntax errors/frustration.

Perhaps you can tell us exactly the status of your database and development, and how your structure and approach uses the helpful info you said you received from AWF.

Good luck with your project.


After the original post:
Edit: I have looked again at your database, and am not sure what exactly you have. Definitely the relationships window doesn't reflect your database.

I gave you an older version... Sorry.

I actually did follow Plog's advice and completely restructrued my tables so I could sum the columns instead of the rows. I have it exactly how he suggested as far as table layout is concerned under "PieceParts" Table.

I followed JHB's post by using the formulas that are in the query "Target Date of Completion." Now, when I try to do a report, I get the message "The wizard is unable to preview your report, possibly because another user has a source table open in exclusive mode. you report will be opened in design view." Then when I click on the report again, I get "Multi-Level GROUP BY clause is not allowed in a subquery."

I don't understand how "Target Date of completion" is a sub-query.

Once I can overcome this problem, I just want scheduling tool that will schedule each piece part or PieceID in sequence based off longest ECD of each part and only load one part on a machine at a time. Hopefully that makes sense...

Here's the new Sheet I have.

 

[jdraw]

What exactly are the objects in your database that show up in the left window with a partially grayed-out font?
When I look at the data in the Report1 properties, it is referencing [Part Pieces] not PartPieces (the revised table structure).

I have looked at a lot of databases, but this one has me confused.

Need more info on what I'm looking at.

 

[JStan89]

What exactly are the objects in your database that show up in the left window with a partially grayed-out font?
When I look at the data in the Report1 properties, it is referencing [Part Pieces] not PartPieces (the revised table structure).

I have looked at a lot of databases, but this one has me confused.

Need more info on what I'm looking at.

Disregard everything in Gray. Those were some older versions that I had initially. I was going to delete them, but was trying to get ideas before I did. They are completely meaningless. If you look at the query "Target Date of Completion", it will not let me do a report on that. I'm confused why it won't let me. Is there any way around that? Thanks in advance.


This has been a lot of trial by error for me haha. I'm self taught (as you can see) on this software.