Updating Table through Query

[mango97]

Hi everybody,

I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.

Here is how the database should handle these events:

1) User clicks button named "requires other parts" when entering a new part.
2) On click a subform is opened that allows the user to enter which other parts need to be completed first.
3) Once all of the parts are completed the next part can begin.

This is my current solution:

1) A query calculates if a part is complete
2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"

I'm stuck on step 2. Any help?

Also, I'm willing to clarify any questions..

thanks,
mango97

 

[MarkK]

You don't need a field named Ready. Consider the possibility that some facts are by their nature the result of a calculation. Think about your age, for instance, and now think about it again. Notice that if you measure precisely enough, you were older the second time.

Some facts are not fixed, but rather, are always the result of a calculation based on other facts. If "Ready" for you is calculated, then always calculate it, and don't store it. That's OK. You never store someone's age.

 

[mango97]

You don't need a field named Ready. Consider the possibility that some facts are by their nature the result of a calculation. Think about your age, for instance, and now think about it again. Notice that if you measure precisely enough, you were older the second time.

Some facts are not fixed, but rather, are always the result of a calculation based on other facts. If "Ready" for you is calculated, then always calculate it, and don't store it. That's OK. You never store someone's age.

Sorry for delay, been away from the internet. I eventually came to this conclusion myself and have been working on a query. Here's how I want it to work:

1) A query calculates if a part has been completed. If it has been completed it shows the date it was completed.

2) A second query calculates how many other parts need to be completed before said part can be started. (I used DCount the number of parts with the same partID)

3) Another field in the second query calculates how many other parts need to be completed ARE completed.

4) Conditional formatting will highlight parts in which the completed parts = required parts.

This should work as it is very similar to how it handles how a part is created. My I've done the first 2 steps, but cannot get the 3rd step to work. I am using the same code in step 2 with an added "AND CompleteDate IS NOT NULL". I get an error that says "Microsoft Access cannot find the name "CompleteDate" you entered intot he expression. Here is my code, and a screenshot of the query:

Completed: DCount("[RequiredPartFID]","PartAssembly","[PartFID] =" & [PartsID] & "AND [CompleteDate] IS NOT NULL")

Any ideas?
Thanks,

mango

 

[MarkK]

Completed: DCount("[RequiredPartFID]","PartAssembly","[PartFID] =" & [PartsID] & "AND [CompleteDate] IS NOT NULL")

If you look at the criteria parameter of your DCount(), note how you've handled [PartsID] and see how that differs from how you've handled [CompleteDate]?

How you've handled [PartsID] is correct.

 

[mango97]

I have a new error now "Syntax error (missing operator) in query expression '[PartFID] = 1ANDIS NOT NULL'." which is followed by error "Unkown".

Here is my updated code:

Completed: DCount("[RequiredPartFID]","PartAssembly","[PartFID] =" & [PartsID] & "AND" & [CompleteDate] & "IS NOT NULL")

Does the fact that the field [CompleteDate] is not calculated from the "PartAssembly" table cause a problem?

 

[MarkK]

In your Criteria parameter you are constructing a string. Make sure that string has spaces between its terms, for instance, "and" should be " and ".

 

[mango97]

Same error as above. Here's the new code:

Completed: DCount("[RequiredPartFID]","PartAssembly","[PartFID] = " & [PartsID] & " AND" & [CompleteDate] & " IS NOT NULL")

 

[MarkK]

How will this render into a string?

"[PartFID] = " & [PartsID] & " AND" & [CompleteDate] & " IS NOT NULL"

Where is the missing space?

 

[mango97]

Where is the missing space?

Whoops. I had it like that, and it wasn't working. Was trying other things. Must have forgot to change it before pasting here. Same error still:

Completed: DCount("[RequiredPartFID]","PartAssembly","[PartFID] = " & [PartsID] & " AND " & [CompleteDate] & " IS NOT NULL")

 

[MarkK]

If PartAssembly is a query, can you post the query text? What is the error?