Question about how to link and lookup data from one table to the next

[adam.grendell]

Hello all.

I run a cellphone/PC repair shop, and I am trying to find a way to keep up with what comes in and goes out of my shop. I want to create a DB that will allow me to keep up with:

Customers
Devices
Repairs

and find a way to link all of these, since they are always interconnected.

For example. In my Customer Table, I have info such as Last, First and Middle name, contact and alternate contact numbers, address, etc., as well as an autonumber primary key. In my device table, I have info such as the IMEI number, brand, model, color, etc., and it too has an autonumber primary key. And finally, for the repair table, I want to use data from both tables, as well as add a few other fields, such as parts used, cost, etc.

My problem is that I don't know if this is simply a table issue, or if I need to make a query to do this, or a fourth table that uses data from all three other tables.

I want to be able to perform lookups on things such as the IMEI number, customer name, or type of repair.

If anyone can give me some pointers, or at least point me in the right direction, I would greatly appreciate it.

On another note, while I'm not new to Access, I am still green on a lot of areas. So all the detail that I can get will be wonderful.

Thanks in advance,

Adam

 

[RuralGuy]

Does the Device Table have a Foreign Key (FK) field with the Primary Key (PK) value from the Customer table so you know who owns it? Does the Repair table have a FK field with the PK value from the Device table?

 

[MarkK]

Well, it looks like you could relate those tables now using a model like . . .

tCustomer
CustomerID (PK)
CustomerName

tDevice
DeviceID (PK)
IME Num

' And a repair connects a customer to a device, like . . .
tRepair
RepairID (PK)
CustomerID (FK)
DeviceID (FK)

. . . where the PK and FK are the primary and foreign keys each table contains.

But for accounting, and keeping track of parts you buy and sell, and quantities of your labour, consider a set of Order/OrderDetail/Product tables, like . . .

tOrder
OrderID (PK)
CustomerID (FK)
OrderDate (when they dropped off their stuff)

tOrderDetail
OrderDetailID (PK)
OrderID (FK)
ProductID (FK) -> 'like your repair table, but includes stuff you sell
Quantity
Price

tProduct
ProductID (PK)
SupplierID
SKU
Description
Price

. . . so this trio takes the place of your repair table, and each itemized part of a repair job, your time, parts consumed and so on, can then be quantified, costed, billed for, and consumed from your inventory, in a very orderly way. And your device table merges into tProduct.

This is a more "industry standard" way of modelling what you seem to be headed towards anyway.

 

[adam.grendell]

Thank you both for replies so quickly.

I have an idea of how I want this to work, but the challenge is trying to put it into words how I want it to come out, and the even harder challenge is trying to make it work in Access.

As far as the ideas you gave, that's pretty much the best info I've been given as of yet. One thing that I'm worried about is how to associate all of those different tables. I'm still a newb when it comes to relationships, and the difference between primary and foreign keys. I know what a primary key is, but a foreign key is well..."foreign" to me. How do I create a foreign key in a table?

And if I have the Customer Table and the Device Table, what is my best route of pulling data from those two tables to create data in a Repair Table? Is it making that data type a lookup field? Do I need to make a query? And as for the relationships, what type of relationship do I need to create to which table?

As I said, I'm still new to a lot of the different parts of Access. I'm not asking anyone to do this for me. I'm just looking for the most detailed information as possible, so I can learn about this more as I go.

 

[RuralGuy]

A Foreign Key field simply contains the Primary Key value from another table. Once you have these fields defined and filled in, the Relationship view can help with the relationships. The wizard will create the relationships for you and if you enforce referential integrity (which you should) the system will maintain it for you. Queries will make related tables seem as though they are just one big table.

 

[adam.grendell]

Awesome. Thanks for the 411. I'm gonna take this info and see if I can get this project started.

I love this site

 

[adam.grendell]

What I'm hoping to be able to do with this data is eventually find a way to use Access 2016 as a sort of Point of Sale program. I already have a barcode scanner that I can get to work with Access, so it's just a matter of putting all the pieces of this puzzle together.

Thanks again for all of your help. I'm sure I'll be back with more questions!

 

[jdraw]

Also, see if this free data model from Barry Williams is helpful.

additional info here

Good luck.