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

[garciaroizm]

The way the form looks is exactly the way our paper tickets look like. That's the way management wants it. The multiple material, quantity, hours, etc fields are sometimes used. All jobs have at least 2 employees on them, but there are many jobs where there can be anywhere from 2-12 employees on them and material and other fields will all be used and most times will need more than one ticket.

[jerepois]

Garciaroizm,

The way that your data is stored is very unorganized as said before.

Here is an example of normalizing the data.

Ticket { TicketID, TicketOpenDate, TicketClosedDate }
Companies { CompanyID, CompanyName }
Job { JobID, JobDesc, JobCompleted }
Material { MaterialID, MaterialCode, MaterialDesc, MaterialCost }
Vehicle { VehicleID, VehicleName, VehicleDescription, VehicleBillPerHour }
Employee { EmpID, EmpName, TradeID, EmpPhone}
Trade { TradeID, TradeName, TradeBillPerHour }

The tables above should have no duplication of information.


Once you have broken all your data into tables we need to now create a tables that will track the information


usedVehicle { (VehicleID, TicketID) , VehicleRepair }
UsedEmployee { (EmpID, JobID, TicketID), UsedEmpHrs }
UsedMaterial { (MaterialID, TicketID), UsedMaterialQty}
usedJob { (JobID, TicketID), JobCompleted }
usedCompany { (CompantID, TicketID) }

These tables is how you track your data. Your main tables should be used as a reference. The Primary Keys (ID) fields will allow you to reference back to these tables. The used tables are where you store the actual data, which is the reference identification keys to your main tables. IF you noticed all these reference the TickedID and the ID of another table. This is how you create your primary identifying key for your used tables.

This will allow you to have multiple entries per ticket. So you could have more than one company, Job, Material, Employee, and Vehicle per ticket.

I just quickly threw this together but I hope you get the concept behind this and are able to apply it to your database.

[garciaroizm]

Ok I started a new database, I made all the tables that will be used to reference which are the main tables, then I made the used tables that will hold the data. I made main tables primary key to be ex: materialID, vehicleID, companyID, employeeID, leaseID, and ticketID. Then in the used tables the primary key is ticketID in all tables. Is that correct?

[June7]

I presume these are 1-to-many relationships. For each ticket there can be many materials, employees, vehicles. This means TicketID is primary key in Tickets table and foreign key in the Used tables. Same for the lookup tables. MaterialID is primary key in Materials and foreign key in UsedMaterial.

The primary keys can be autonumber type field. The foreign keys will then be a number (long integer) type field.

[jerepois]

Ok I started a new database, I made all the tables that will be used to reference which are the main tables, then I made the used tables that will hold the data. I made main tables primary key to be ex: materialID, vehicleID, companyID, employeeID, leaseID, and ticketID. Then in the used tables the primary key is ticketID in all tables. Is that correct?

http://office.microsoft.com/en-ca/ac...001054693.aspx

This link will explain how you can make both the Reference ID ex.(MaterialID) and the TicketID to be the Used tables Primary key. This will create a 1-M relationship where only one type ex.(MaterialID) can be used per ticket but is able to be applied to more than one ticket at a time. Product numbers rarely change in my experience. I would make the two Foreign keys the primary key. It makes sense if you do not need any more columns.

[ItsMe]

This link will explain how you can make both the Reference ID ex.(MaterialID) and the TicketID to be the Used tables Primary key.

I would advise against someone starting out to use a Composite or Compound key.

[jerepois]

I guess, but either way he still needs those fields and can use a autonumber field for his Usedtables primary key

[June7]

The Used tables don't need primary key if they don't have related child tables. However, setting the two fields as compound key (or at least a compound index) will prevent duplicating the pairs.

[garciaroizm]

Ok I think I got it... Companies table companyid is primary key, in usedcompany table companyid is foreign key. That goes for all tables. Do I need a usedticket table?

[June7]

No. Users do not 'pick' a ticket during data entry, like they 'pick' employee, material, vehicle. Data entry initiates a ticket record and users select the resources to associate with that ticket.