Counting objects in a query.

[Nemo]

Hey guys. Thanks for taking the time to read this. I'm new to Access so I may say things I know nothing about. I have a situation where I have a production line. Each station has three motors and each motor has to have three cables. Each one of the three can be different types. What I would like to do is sum up the quantities of each type of cable for all entries. Sounds simple enough but it still keeps baffling me. Please take a look at my screen shot. Thanks

[jwhite]

You should have another table called PanelDataMotors as a child table to PanelData. Then in the child data you would have a field for MotorNumberID (primary key for table), PanelDataID (primary key value in tblPanelData), MotorNumber and MotorType. Then it would be easy as pie to get a count. Also I see your table view has a combo box - please review - http://access.mvps.org/access/lookupfields.htm. And if you have spaces in your field names, remove them, no special characters either.

[Nemo]

Thanks JWhite. I'll give this a try.

[jwhite]

You probably should have another table for motor types, tblMotorTypes (MotorTypeID, MotorType), then in tblPanelDataMotors, have MotorTypeID instead of MotorType.

[Nemo]

So what your saying is I need to make another table but still link the field to the entry form. Hence the child data table.

[jwhite]

Yes, but instead of linking (storing) the actual value, you will save the Primary Key of the selected MotorType in tbl PanelDataMotors. Then in your form you would have a combobox for MotorType (cboMotorTypes) with fields MotorTypeID and MotorType. This will allow you to easily add more motor types in the future without altering any code or table design.

[Nemo]

Yes, but instead of linking (storing) the actual value, you will save the Primary Key of the selected MotorType in tbl PanelDataMotors. Then in your form you would have a combobox for MotorType (cboMotorTypes) with fields MotorTypeID and MotorType. This will allow you to easily add more motor types in the future without altering any code or table design.

You lost me with this one. You say "with fields MotorTypeID and MotorType." I am possibly going to have a maximum of 8 motors per station. Then I would choose the motor type cable. I get the combobox for the type but you lost me on the motor type id. Here's a screenshot of the tables.

[orange]

Nemo,

Do you have a clear statement of requirements that you can share with users? As jwhite has advised, you appear to have a data structure issue. Your tables are not normalized, and in order to determine the required relationships, readers need to better understand your business and processes.

Here are some links to data base planning and design info.

Good luck.

[jwhite]

Instead of MotorType in PanelDataMotors, you would have MotorTypeID - FK to the selected record in MotorTypes. You have another repeating group of fields in [Panel Data Table] for Port(n) - these should be in another table as well. It might would be a a pain, but I would correct the naming of your objects now before going further - no spaces in object names and use industry-standard prefixes - review: https://ss64.com/access/syntax-naming-variables.html

[orange]

Nemo,

If you marked this thread solved, could you show readers your solution? It may help someone else who has/had similar issue.

[Nemo]

Nemo,

Do you have a clear statement of requirements that you can share with users? As jwhite has advised, you appear to have a data structure issue. Your tables are not normalized, and in order to determine the required relationships, readers need to better understand your business and processes.

Here are some links to data base planning and design info.

Good luck.

This application is used for keeping track of how many different cables I use in an automotive production floor. What I'm trying to do is create a simple database that keeps track of a how many different types of cables I use to connect to a motor for a linear motor drive system. For example: I have 10 stations on one body weld line. Each station has a control Panel with an ID. Each station has three motors or four. For this example we'll keep it to three. Each motor needs a cable. That cable length can vary depending on the distance of the control panel to the motor. Also the type of cable can be either a static type or a high flex type. What I would like my database is to keep track of each style of cable for each station and give me a total of each style of cable based on length and type. Again, I'm learning as I go and I have a lot of going to do.

[orange]

Good stuff.

Here is what I gleaned from your description.

Your basic business is keeping track of how many different cables I use in an automotive production floor

Factors:
types of cables
connect to a motor
10 stations
body weld line
Each station has a control Panel
Each station has three(or four) motors .
Each motor needs a cable.
cable length varies with the distance of the control panel to the motor
type of cable can be either a static type or a high flex type
style of cable for each station

Goal/objective:
produce total of each style of cable based on length and type

Most readers are not in the same "business" as you, so we have some trouble understanding how these things/factors relate to one another.

Readers will advise you to write a clear description of your business in plain English. Review/research table design and normalization; then build a draft model of your tables and relationships; test your model with sample data and what if scenarios.
Adjust your model as necessary. When all is working on paper, then design your database using your model as a blueprint.

Good luck.

[Nemo]

Finally. I was finally able to figure out what to do when using combo boxes for selections. The rule is NOT to use a combo box in the main storage table. I created a lookup table to pick from and then that value is placed into the main table as text without it being a combo box selection. Once I developed "lookup" tables I was then able to create queries that added up quantities using simple summation formulas. The lookup tables also allowed me to add or delete selections without affecting other components of the main data table. Thanks to all who have helped me with this issue. Live and learn but mostly learn.