michaelxmas
New member
- Local time
- Today, 21:37
- Joined
- Nov 2, 2012
- Messages
- 6
Hi all. How's it going? I'm new here, but I like to think of myself as being reasonably good with Access. However, I'm totally stuck with a multiple DCOUNT and would really appriciate any advice... I'll give the formula, and the background is below if you're interested....
This works as a single criteria:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])
These don’t:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])
Error "The specified field [WC Des] could refer to more than one table in the FROM clause of your SQL statement."
If I specify the table ‘export’ I get error Extra ) in query expression ‘export.[DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])]
If I add export to the query text so that it reads Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [export]![wc des])
It works, but counts ALL the records, not just those that meet both criteria.
Does it matter that [Next WC] and [WC Des] are fields whereas “midpartsusedin” and “mid pack” are both expressions within queries???
I’ve tried all different combinations of “ & AND []() for example saying; [midpartsusedin]=[midpack], but no other combination except "midpartsusedin =" & [midpack]) seems to work
Getting nowhere with this…. Anybody know the correct formula or possible solutions.
********
Background.... I get a huge list of production orders every day in excel - which curently we print and hand out to the various machine operators (Work Centers) in the factory. We then have a supervisor manually add up the time to make each order to calculate demand vs. capacity to determine how many people we need to run each machine and whether we need overtime or not. Then finally when complete we type these orders back into a computer and fill in another sheet as a QA record.
So I want to display the orders in Access, avoid all the printing and distribution time, automate the demand/capacity calculations, avoid lost and missing orders and eliminate a lot of the duplicated admin. Savings should be about 8-16 hours a day.
So far so good, but here's where I'm stuck. In the paper system, when a order for a 'sub-part' is complete, the printed order is walked to the next machine. When all the sub-parts are made, this is the trigger to that work center that says the raw material parts are available for them to assembly into a finished product.
In the database, I just want the machine operator to tick a box when the sub-part order is complete. When all the sub-parts are ticked complete, this should automatically mark the order in final assembly appear as "available".
Each line in the spreadsheet is an order (Midpack), giving the product name and qty to make, time to make, what department & machine (WC) to make it on, what machine to move it to next for further processing and what product it is used in (Midpartsusedin) - which is another line in the spreadsheet, except for the finished product where Midpartsusedin = Customer,
Midpack / Part / Qty / Time / Department / WC Des / Next WC / Midpartsusedin
So I have a query that counts all the records where midpartsusedin = midpack. This tells me how many sub-parts are required for the order.
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])
Then I have query that counts all the records - in a 'complete' query where midpartsusedin = midpack. This tells me how many sub-parts have been complete.
Available: DCount("*","works order query availability complete","midpartsusedin =" & [midpack])
When required > available the form puts a red cross next to the order. When required = available then the order gets a green Y on the form.
I then do a sum of the total time of all the "available orders". So that if a work center is running out of work (say less than 30 mins available work), it flags up warning highlighting the need to expedite sub-parts to that work center. This keeps work flowing between the work centers.
I need to add a second criteria to the DCOUNT so that it finds the required and available sub-parts where the [midpartusedin] = [midpack] AND where the [WC Des] = [Next WC]. This also makes sure it only counts 1st level sub-parts, not all the sub-part right through the supply chain. Strangly the formula doesn't seem to work if I used the square brackets as per the other DCOUNT advice I've seen.
Been banging my head against a wall for about 2 days on this now. :banghead:
Wouldn't be surprised if the answer is obvious.
Thanks for any help!
Mick
This works as a single criteria:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])
These don’t:
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])
Error "The specified field [WC Des] could refer to more than one table in the FROM clause of your SQL statement."
If I specify the table ‘export’ I get error Extra ) in query expression ‘export.[DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [wc des])]
If I add export to the query text so that it reads Required: DCount("*","works order query availability","midpartsusedin =" & [midpack] AND "Next WC =" & [export]![wc des])
It works, but counts ALL the records, not just those that meet both criteria.
Does it matter that [Next WC] and [WC Des] are fields whereas “midpartsusedin” and “mid pack” are both expressions within queries???
I’ve tried all different combinations of “ & AND []() for example saying; [midpartsusedin]=[midpack], but no other combination except "midpartsusedin =" & [midpack]) seems to work
Getting nowhere with this…. Anybody know the correct formula or possible solutions.
********
Background.... I get a huge list of production orders every day in excel - which curently we print and hand out to the various machine operators (Work Centers) in the factory. We then have a supervisor manually add up the time to make each order to calculate demand vs. capacity to determine how many people we need to run each machine and whether we need overtime or not. Then finally when complete we type these orders back into a computer and fill in another sheet as a QA record.
So I want to display the orders in Access, avoid all the printing and distribution time, automate the demand/capacity calculations, avoid lost and missing orders and eliminate a lot of the duplicated admin. Savings should be about 8-16 hours a day.
So far so good, but here's where I'm stuck. In the paper system, when a order for a 'sub-part' is complete, the printed order is walked to the next machine. When all the sub-parts are made, this is the trigger to that work center that says the raw material parts are available for them to assembly into a finished product.
In the database, I just want the machine operator to tick a box when the sub-part order is complete. When all the sub-parts are ticked complete, this should automatically mark the order in final assembly appear as "available".
Each line in the spreadsheet is an order (Midpack), giving the product name and qty to make, time to make, what department & machine (WC) to make it on, what machine to move it to next for further processing and what product it is used in (Midpartsusedin) - which is another line in the spreadsheet, except for the finished product where Midpartsusedin = Customer,
Midpack / Part / Qty / Time / Department / WC Des / Next WC / Midpartsusedin
So I have a query that counts all the records where midpartsusedin = midpack. This tells me how many sub-parts are required for the order.
Required: DCount("*","works order query availability","midpartsusedin =" & [midpack])
Then I have query that counts all the records - in a 'complete' query where midpartsusedin = midpack. This tells me how many sub-parts have been complete.
Available: DCount("*","works order query availability complete","midpartsusedin =" & [midpack])
When required > available the form puts a red cross next to the order. When required = available then the order gets a green Y on the form.
I then do a sum of the total time of all the "available orders". So that if a work center is running out of work (say less than 30 mins available work), it flags up warning highlighting the need to expedite sub-parts to that work center. This keeps work flowing between the work centers.
I need to add a second criteria to the DCOUNT so that it finds the required and available sub-parts where the [midpartusedin] = [midpack] AND where the [WC Des] = [Next WC]. This also makes sure it only counts 1st level sub-parts, not all the sub-part right through the supply chain. Strangly the formula doesn't seem to work if I used the square brackets as per the other DCOUNT advice I've seen.
Been banging my head against a wall for about 2 days on this now. :banghead:
Wouldn't be surprised if the answer is obvious.
Thanks for any help!
Mick
