Newbie: Multiple records to one customer

This I am sure is a newbie question, but one that I am
having a hard time with. I am creating a repairs database
and I have a table for my repair tickets, for my
customers, for my repair locations, and for my parts. The
problem I am having is when doing a repair I have some
(repairs) that use multiple parts. I want the tech to be
able to choose from a menu the parts(from the parts table)
that they use. And like I said, there will be multiple
parts.

Right now I have it set up as the following:
Repair Table:
there is a field for repair parts.

Parts Table:
Field: PartNumber, text (key)
Field: PartDescription, text
Field: PartVendor, text
Field: PartWholesale
Field: PartRetail

Please help. I think I might be going at this the wrong
way, but I am not sure what the right way is.

Thanks,
Bill Craig
Leesburg Hobbies & Collectibles

Hey Bill,

It looked to me like this was a classic order entry type app. I was able to create a new db from template (order entry). The Products table in the template is very similar to your Parts table. You might want to make up a sample db using the template just to see how the various parts (tables, forms, etc) work together.

As far as your actual parts table structure, I'll suggest just a few changes:

tblParts 'do a search on naming conventions for explanation of this.....
PartNumberID 'autonumber PK
PartDescription 'text
VendorID 'foreign key from a vendor table
PartWhsle 'currency
PartRetail 'currency

hth
--
rpw


"Bill Craig" wrote:

This I am sure is a newbie question, but one that I am
having a hard time with. I am creating a repairs database
and I have a table for my repair tickets, for my
customers, for my repair locations, and for my parts. The
problem I am having is when doing a repair I have some
(repairs) that use multiple parts. I want the tech to be
able to choose from a menu the parts(from the parts table)
that they use. And like I said, there will be multiple
parts.

Right now I have it set up as the following:
Repair Table:
there is a field for repair parts.

Parts Table:
Field: PartNumber, text (key)
Field: PartDescription, text
Field: PartVendor, text
Field: PartWholesale
Field: PartRetail

Please help. I think I might be going at this the wrong
way, but I am not sure what the right way is.

Thanks,
Bill Craig
Leesburg Hobbies & Collectibles

Just one more thing....

The VendorID (the foreign key) field should be 'Long Integer' so that the field type will match the autonumber PK of the vendor table.

--
rpw


"rpw" wrote:

Hey Bill,

It looked to me like this was a classic order entry type app. I was able to create a new db from template (order entry). The Products table in the template is very similar to your Parts table. You might want to make up a sample db using the template just to see how the various parts (tables, forms, etc) work together.

As far as your actual parts table structure, I'll suggest just a few changes:

tblParts 'do a search on naming conventions for explanation of this.....
PartNumberID 'autonumber PK
PartDescription 'text
VendorID 'foreign key from a vendor table
PartWhsle 'currency
PartRetail 'currency

hth
--
rpw


"Bill Craig" wrote:

This I am sure is a newbie question, but one that I am
having a hard time with. I am creating a repairs database
and I have a table for my repair tickets, for my
customers, for my repair locations, and for my parts. The
problem I am having is when doing a repair I have some
(repairs) that use multiple parts. I want the tech to be
able to choose from a menu the parts(from the parts table)
that they use. And like I said, there will be multiple
parts.

Right now I have it set up as the following:
Repair Table:
there is a field for repair parts.

Parts Table:
Field: PartNumber, text (key)
Field: PartDescription, text
Field: PartVendor, text
Field: PartWholesale
Field: PartRetail

Please help. I think I might be going at this the wrong
way, but I am not sure what the right way is.

Thanks,
Bill Craig
Leesburg Hobbies & Collectibles

Bill,
You will need a third table -- let's call it RepairDetails -- and you will
need to put the field for the RepairParts in that table. Thus you would have
this structure.

Repairs
RepariID (PK)
RepairDate
Customer
---Other fields related to repairs

RepairDetails
RepairDetailID (PK)
RepairID (FK)
PartID (Look this up from the parts Table)
--Other fields related to repair details

Parts
PartID (PK)
--other fields related to parts.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Bill Craig" wrote in message
...
This I am sure is a newbie question, but one that I am
having a hard time with. I am creating a repairs database
and I have a table for my repair tickets, for my
customers, for my repair locations, and for my parts. The
problem I am having is when doing a repair I have some
(repairs) that use multiple parts. I want the tech to be
able to choose from a menu the parts(from the parts table)
that they use. And like I said, there will be multiple
parts.

Right now I have it set up as the following:
Repair Table:
there is a field for repair parts.

Parts Table:
Field: PartNumber, text (key)
Field: PartDescription, text
Field: PartVendor, text
Field: PartWholesale
Field: PartRetail

Please help. I think I might be going at this the wrong
way, but I am not sure what the right way is.

Thanks,
Bill Craig
Leesburg Hobbies & Collectibles