Return text after querying multiple checkboxes

barter27293

Registered User.
Local time
Today, 21:28
Joined
Dec 5, 2016
Messages
10
Hello,

I'm currently working on a database to track items through a manufacturing plant, so have multiple checkboxes that are ticked once a process is complete.

I have created a query that has a calculated field, that I want to return a text value depending on what stage it is at, however the difficulty is - not all processes are required, so it needs to check the previous processes first if they are ticked to be required and then return the value.

I was getting on fine - and what I had done so far was working, however the IIf statements were starting to get very long and it then refused to let me enter any more... and subsequently found there is a limit to the length of expressions.

If anyone has any suggestions on how I could do this differently - it would be much appreciated.

Here is what I had done so far:

Code:
Status: IIf([delivered]=Yes And [tobefitted]=Yes,"Fitting",
IIf([delivered]=Yes And [tobefitted]=No,"Complete",
IIf([req_60]=Yes And [60_finished]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [50_assembled]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [req_50]=No And [40_cnc]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [req_50]=No And [req_40]=No And [30_machined]=Yes,"70 Packing And Delivery",
IIf([req_50]=Yes And [50_assembled]=Yes And [req_60]=Yes And [60_finished]=No,"60 Finishing",
IIf([req_40]=Yes And [40_cnc]=Yes And [req_50]=No And [req_60]=Yes,"60 Finishing",
IIf([req_30]=Yes And [30_machined]=Yes And [req_40]=No And [req_50]=No And [req_60]=Yes,"60 Finishing",
IIf([req_40]=Yes And [40_cnc]=Yes And [req_50]=Yes And [50_assembled]=No,"50 Assembling",
IIf([req_30]=Yes And [30_machined]=Yes And [req_40]=No And [req_50]=Yes,"50 Assembling",
IIf([req_20]=Yes And [20_programmed]=Yes And [req_30]=No And [req_40]=Yes And [40_cnc]=No,"40 CNCing",""))))))))))))

Ive come up against a brick wall :banghead:... and cant seem to find away around it. Any help would be greatly recieved.

Thanks, Kieran
 
I think you need to start from scratch and really think out your process. The fields you have posted in your code indicate you haven't structured your tables correctly.

Fields (nor tables) should be named after actual values nor numerated. [req_30], [req_40], [50_assembled] shouldn't be field names but instead should be values stored in the table itself.

I believe you will probably need to build a system to determine which processes are needed for each item. From there you could build out a plan for each item and then be able to check off each step in the plan as it is completed.

Can you provide more specific information about your processes and how you determine what steps an item needs?
 
Plog - Thanks for the reply... however I'm not entirely sure I understand what you mean by these values being stored in the table itself.

Basically - Each item that needs to go through the factory will be looked at and someone will decide which processes it needs to involve, and I was wanting a checkbox for them to be able to input this - the main processes are:

00 - Drawing
10 - Job Pack
20 - Programming
30 - Machining
40 - CNCing
50 - Assembly
60 - Finishing
70 - Packing and Delivery

Then depending on what the item requires will determine which department will receive the information, then it needs to have another checkbox for each department to mark it as complete once they have done it.

I hope that is clear enough... what way do you suggest is best to structure the tables etc to do this?

Many thanks
 
Each item that needs to go through the factory will be looked at and someone will decide which processes it needs

The way to determine what processes an item needs requires a human? Maybe that's true in your case, but I challenge you to think about it. That human makes their decision based on something, could that something be turned into logic and applied by a computer?

Those steps you listed--that's how your table should look, not with those values used as field names. You should have a table to store those steps like so:

ProcessSteps
StepNum, StepName
00, Drawing
10, Job Pack
...
etc.

Then you create another table that determines which steps each item would need. In general that table would look like this:

ItemSteps
ItemStepID, autonumber, primary key
ItemID, number, foreign key to Item table to denote which item the step is part of
StepNum, text, foreign key to ProcessSteps.StepNum to denote what step it is

A best practice would be to have StepNum be numeric, but you provided values already, so I used those. The ItemSteps table could also hold a completion field which could either be a date or a Yes/No which would let you know when/if each step was completed.
 
Ok Thanks for that - I think I just about get the idea.

Due to the nature of how we operate - I don't think this could be automated... we receive drawings from customers by PDF and have to analyse them and input them into the system from there!

Now I have a table listing the types of processes - how do I create a form for inputting items that lists these processes with checkboxes beside them - currently it is only showing available fields for the form as the 'StepNum' and 'StepName' field names not the processes themselves.

(The way I had numbered the processes is standard practice in manufacturing - allowing the processes themselves to be split further... ie machining can then be split between machines - 31,32 33 etc)
 
Your "numbering" system is fine, I'd just suggest you use actual numbers (e.g. 01 -> 1). But if somehow you have one that is 98A, then maybe you do need to use text.

The form the user would see would actually be a main form/subform system. The main form would be based on your Item table and then the subform would be based on ItemSteps table I described earlier. They would be tied to each other via a child/parent relationship on their ItemID fields.

The subform would be a continous form with one drop down on it (not a checkbox). That drop down would be based on ProcessSteps and feed into the StepNum field of ItemSteps. The user would then select a step and move to the next row of the subform and add another step if necessary.
 
Ok excellent - this is coming together - so thanks a lot...

However - just wondering, is there any way to work it so when you create a new item, it as default applies all the processes, and then you can remove the ones that are not needed?
Partly the reason I was trying to use checkboxes initially is because I was wanting to set the default option to checked, and then we can just de-select the processes that are not required as most items need the majority of them and maybe just one or two missed out.

Sorry if I didnt really mention this before - but it only dawned on me as I set up the form and am having to select each process from the dropdown one by one, which is a bit tedious especially when you may have 20+ items to enter with every process required.
 
However - just wondering, is there any way to work it so when you create a new item, it as default applies all the processes, and then you can remove the ones that are not needed?

Yes, that would require VBA, though. What you would do is run an APPEND query when the new Item was added. That query would take the new ItemID and add however many step records to ItemSteps. Then, using the same form I mentioned above it would populate with that data and the user could delete the ones they didn't want.
 
Ok yes I guessed it would need to be in VBA... I'll have to play with that a bit.

I now haven't yet actually got to what I was initally trying to do... ie having a single field to allow grouping per department on a report... I guess now the process of doing this will be totally different to the IIf statments I was doing before?? Whats the best way of approaching this?
 
I'm not really certain what you mean by "allowing grouping per department". Could you show an example of what you want your report to look like?
 
With my initial IIf statement - it was obviously outputting it in one field, which allowed me to then run a report on the query and group it by this field.

Therefore this produced a report grouped into the processes required and listed under each process the items that needed that process done to it - if that makes sense!

Ie. once the "machining" checkbox was ticked for an item - the calculated field would then show "assembly", so when a report is run the item now shows in the "Assembly" area of the report.

Hopefully the attached shows a bit of a basic idea of the format that I am eventually trying to achieve.

Thanks for your ongoing help!:D
 

Attachments

barter,

Stepping back for a moment and just thinking about the business, do you have a model or picture of the processes involved in performing the business?
Are there X types of Jobs that can be scheduled?
It seems to me you understand the processes and are "hell bent" to get something working in Access.
I make that statement based on your post but not knowing anything about you or the business; and I could be totally off base.

To me it sounds similar in concept to a "pick list".

You have a number of items/assemblies (and in your case processes/subprocesses??). When a new order/requirement comes in; someone identifies the things (items/assemblies) that must be "picked" from the warehouse and gathered together to fill the Order.

It's not a perfect analogy, but the key point is you have many steps(small processes/things to do) and not all are required for any given "job". The important thing is to select out the proper steps (from all possible steps) in order to schedule/complete the "job" effectively and efficiently.

I think the database and Access stuff will come together once you analyze your processes and steps and get a clear picture of your business.

But as I said, I may be way off base.

Good luck.
 
Sounds like you want to know what step in the process an item is in and what the next step will be. Yes, that will be possible with your new tables as long as:

1. You have a way to order your steps. As long as StepNum can be used to determine how the process should work (StepNum=01, then StepNum=02, then StepNum=03) that field is the only one you need. If that is not true (sometimes StepNum=10 comes before StepNum=14, sometimes it comes after) then you would need an additonal field in ItemSteps to determine the order of the steps.

2. ItemSteps includes that field I talked about denoting if a step was completed or not. This would be either a Date or a Yes/No field, depending on if you need to know when a step was completed, not just that it was completed.
 
Plog - Thanks a lot indeed for all your help with this. I have now just about got a report in the format that I want... so I'll stop pestering now and try and work out the rest myself ;)

Thanks again.

Kieran
 
jdraw,

Thanks for taking the time to read my posts and reply.

Yes we have got a fairly clear picture of the processes that are required and the information that needs to be captured at each step. We are a joinery company producing completely bespoke items such as stairs, windows, cabinets etc. and there are never any products the same!

Before now we have been using a mash up of spreadsheets to try and keep track of everything, but I was starting to get frustrated with trying to tie them all together and easily create reports etc, There doesn't seem to be any software out there currently that is bespoke/flexible enough for what we need, so I have decided to try and start from scratch with Access... maybe I am 'Hell Bent' on using access!!... but currently I don't have any experience in any other software that can do what I have in mind!

The main process currently is as below:

Salesperson gets enquiry and enters list of items along with customer information onto the system
V
Estimator analyse's items and selects which processes are required and enters estimated hours for each item
V
Estimator produces quote for customer based on estimated hours

Once order is received -

Items get assigned a order number
V
Information pack for the item is produced and given to the dept. of the first process that it requires
V
between each process the actual hours used in the previous process are inputted back into the system to allow tracking of efficiency etc. and to update where the item is at in the factory.

The above information is needed to be used to produce daily and weekly reports as to what is currently in the factory so we can estimate delivery dates and set priorities.

I hope that makes it clear what we are trying to achieve - and I would welcome any suggestions as to ways to do this.
 
Kieran,

There are things you can do to get the necessary facts to design and build your database.
I'm going to suggest some video tutorials by Dr. Soper regarding the concepts of Database
, Relational Model and Data modelling.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Here is another tutorial to work through from RogersAccessLibrary. You have to work through it to get the benefit of the procedure. He starts with a clear description of the business and the things involved, then takes you through a process to identify tables and relationships. Getting your tables and relationships designed to meet your business requirements is critical to database. THis will result in a blue print for your database. Design is also the step most newbies glance over or ignore--only to run into major difficulties down the road.

Also, I'm going to suggest you watch 3 -5 of the "KNUGGETS" from B-A Experts on youtube. They are factual and funny, but hit key points in analysis.

Google youtube ba-experts analysis and look for requirements, business analysis and DFD. They are short and to the point and serve as a great start for ideas and approaches to getting your facts organized.

Good luck. and don't be too quick to start coding--get your design started and get it vetted by colleagues. Test your model (pencil and paper)and make sure it works. Adjustments are common as details evolve.

Here is a link to my stump the model approach.

Good luck with your project.
 
jdraw,

That is very helpful - thanks a lot for all those links. Yes I have been trying to map out the processes and relationships etc. but I probably still need to think it all through again after reading up a bit more about it!

Ive been mocking up the system with colleagues in excel and refining that over the past few months, just to try and work out all the bits of information that are needing to be inputted and get a full list of fields required... and we have almost got that sorted now, but being excel I keep coming across limitations on what can be done automatically.

I was planning on doing something similar to your so called 'Stump the model' approach by getting something working and then getting the different departments to test it in different scenarios before finally implementing it.

Thanks
 

Users who are viewing this thread

Back
Top Bottom