Query Form combo box problem

[PG1]

Hello Access Gurus,


I need help.
Background: I am storing date, an operation name (weld, drill, etc), part number, Machine number, Quantity into a table called Production. Part number, Operation name, machine numbers are each listed in their own tables. When I enter data via a form into the table, I store the date, actual part number, the operation number ID (1 for weld, 2 for drill, etc), actual machine number (text), and quantity.


Here's the issue: I have a form that runs a query to pull out quantity run by operation on a particular date. The query returns a blank because I can't figure out how to have the form combo box display 'weld' but actually query '1' from the table.

 

[jdraw]

Show us a jpg of your relationships window - or - post a copy of your database in zip format.

 

[PG1]

Here's the jpeg


Thanks!

 

[jdraw]

You told us HOW you have set up to do something, but in simple English WHAT is the something.
It seems that you make Parts and in doing so there are different oprations that may/do involve different machines. But I'm just guessing. It would be better if you told us the business and processes involved in simple terms --just as you would tell someone who you wanted to build this proposed database. It is also better for communications if you use names and not your jargon (PN,OP..) so we understand your business and your specific needs.

You should also be aware that Access has a number of reserved words that you should avoid as names. Date is one such word.

 

[PG1]

Ok, sorry for confusion. Yes, we are a manufacturing plant. We currently record out production and fallout (defects) in multiple excel spreadsheets. This makes calculating yields difficult. I am working on having this data entered into Access so that we can query the data by date, name of production operation (weld, hole drill, print, etc), part number (1000, 1001, etc), and machine number (ws1, ws2, hd1, hd2, etc). I have production data being entered via a form that has combo boxes in which two are related meaning the second is dependent on the first. That is combo box 'CboOpName' for the name of the production operation. This then drives a combo box 'CboMachNo' for the machine number to only display those machines associated with the particular operation selected in the CboOpName combo box. What happens is that the operation name field is recording the ID number for that operation name. That's ok until I try to develop a query to pull out how many pieces were made on a particular date by a certain operation. I want the query form combo box to show the operation name (weld, hole drill, etc) but the data is the ID number for that operation (1, 2, 3, etc). So somewhere there has to be a way to have the combo box refer to the ID number while still displaying the name.

 

[jdraw]

Can you tell us a little more about the PartNumber?
Do you have Customers that Order Parts? If so, is that not part of your database??

Can you clarify the following? Revise/rewrite as necessary.

We need to make Part 3000, It will require these operations Cutting, drilling and welding.
We can do cutting on machineNumber M100 or M300.
Drilling can only be done on ws400.
Welding can be done on W230, w340 OR ws700.

What exactly would you be searching for or calculating if you manufacture
400 of Part 3000 during June 2018?

I'm just trying to understand your set up and requirement.

 

[mike60smart]

Hi

If you can upload a zipped copy of the Db it will make life a little easier to give you a solution.

 

[PG1]

Can you tell us a little more about the PartNumber?
Do you have Customers that Order Parts? If so, is that not part of your database??

Can you clarify the following? Revise/rewrite as necessary.

We need to make Part 3000, It will require these operations Cutting, drilling and welding.
We can do cutting on machineNumber M100 or M300.
Drilling can only be done on ws400.
Welding can be done on W230, w340 OR ws700.

What exactly would you be searching for or calculating if you manufacture
400 of Part 3000 during June 2018?

I'm just trying to understand your set up and requirement.

I have two main tables - one for production and one for quality. Production will enter: Date part was made, part number made, quantity of that part number made, operation that was performed. Quality will enter date a part was rejected, part number of the reject, operation the reject came from, failure type, and quantity of rejects.


Several searches would be done:
1. How many pieces of a particular part number were made on a day for a particular operation. I can then see it being broken down further by machine number in the future.
2. How many pieces of a particular part number were rejected on a day from a particular operation. I can then see it also being further broken down by machine number in the future.


I can pull the data from the query design because I know the format that it was entered. What I'm attempting to do is create a query form that has drop downs to select from. I already have forms to enter the data into the tables and they work well. Now I'm working on getting the info out.


I appreciate the help.

 

[PG1]

Here's a zip of the file.

 

[jdraw]

You can get the name rather than the number in the Production form
by changing the format (Column width) as follows

Just looking at your database.
You have some sort of Job/WorkOrder that we haven't discussed.
It also seems you have/may have a list of
-OperationsRequiredByPart
-OperationsThatCanBePerformedByMachine
-MachineType
-ProductionOperationCompleted
-QualityControlStandardByPart
-PartProductionQualityAcceptance/Rejection

 

[PG1]

When I change the criteria on the 'Production Query' to reference any of the combo boxes (for example: [Forms]![Production Query Form]![CboJobNo]) the result of the query is blank.

 

[mike60smart]

Hi

Take a look at the relationships between Production , Opname and MachineNo

I changed the fieldnames of Opname and MachineNo in the ProductionTable so that they are now Numbers ie OpNameID and MachineNoID

I then created a query based on the Production Table.

The Command Button on the Production Query Form now has some VBA in the ON Click Event vice an embedded macro.

See if this suits your requirements.
View attachment Production_Database_with Search.zip

 

[PG1]

Thank you. I'll take a look.