I need help with certain records to show up in certain combo boxes

[garciaroizm]

Here is a copy of my database. I have no code yet.

[June7]

What do you mean by 'certain records to show up in certain combo boxes'? Maybe you want cascading (dependent) comboboxes. Review: http://www.datapigtechnologies.com/f...combobox2.html

[garciaroizm]

I'm not sure June7, if you look at my db I have a form, in that form at the bottom I have certain text boxes labeled master electrician, construct. Electrician, journeyman electrician, etc... Next to that are the combo boxes that are linked to the employee table that has there name and title... I want records to come up under master electrician in that combo box for only master electrician and so on...

[June7]

If you want only employees classified as electrician in the combobox list, then use a WHERE clause in the RowSource SQL.
SELECT Employees.[Employee ID], Employees.[Employee Name] FROM Employees WHERE [Job Status] LIKE "*Electrician*";


However, your database structure is not normalized. Multiple similar name fields for the same category of data is an indication of this. Are you sure you want to be limited to 22 material codes, 5 service trucks, 3 construction electricians, etc. ?

This structure will give you a lot of issues in searching and sorting for material used, or electricians who worked, etc. Any sort of statistical analysis will be made more difficult and probably require a UNION query to manipulate the data to a normalized structure.

Notice the Ditcher/Excavator combobox shows an error because of the / character in the field name. Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[garciaroizm]

Ok I understand that, so I add the where clause to every combo box to lookup certain job title. There is over 3000 material and material codes I deleted them to make db smaller

[June7]

Doesn't matter how many codes there are in Material table. The Tickets table is limited to 22 materials per record. Maybe 22 is more than enough, often it will be too many and many fields will be blank. This is not a normalized data structure. Maybe this suits your needs just fine but should be aware of the difficulties this can cause.

[garciaroizm]

How would I structure it to be more sufficient.

[June7]

Would need related tables for each of the data categories. Tables for MaterialsUsed, PersonnelWorked, TrucksUsed, etc.

[garciaroizm]

Ok so I make those tables and I still need the tables I have currently... Material, vehicle, employee, etc.. Those tables I create how do I relate them... I'm just not understanding how to structure my db...need serious help.

[June7]

The existing tables (Material, Vehicle, Employee, etc) would be 'lookup' tables used as sources for selecting data for the Tickets table and related tables to document resources utilized for each ticket.

This is basic relational database design concepts. Perhaps this will help http://www.rogersaccesslibrary.com/

[garciaroizm]

Ok thanks I will take a look at it and get back to you with any questions if that's ok

[garciaroizm]

Tessco Ticket - Copy.zipOk so I made those tables now, I should now relate them by whatever name in the column.. ex: Material table has material, material code so I relate them to the materialused table I created to material and material code, etc.???

[June7]

You still do not appear to have a good understanding of relational database design. Did you get anything from the referenced link? I don't see how you could have had enough time to get through tutorials.

Your new tables are for associating Ticket record with the resources utilized. Need fields in MaterialUsed for the TicketID and MaterialID (or MaterialCode - whichever you decide to save should be designated the primary key in Material table). Do not need Material field in MaterialUsed. Similar for EmployeeWorked and VehicleUsed.

Now use form/subform arrangement for data entry. Main form bound to Tickets and subforms bound to the new tables. Use comboboxes in subforms to select resources from the 'lookup' tables. http://office.microsoft.com/en-us/ac...010098674.aspx

[garciaroizm]

June7,
Can you look at this database and see if I'm on the right track and what I need to do next. Thanks for all the help.

[June7]

You still have the multiple material, quantity, hours, operator fields in Tickets table. This is not a normalized data structure.

Don't need separate fields for Complete and Incomplete. A ticket can be only one or the other. Use one field called Complete.

The form is not what I described.