Database structure

  • Thread starter Thread starter jw007
  • Start date Start date
J

jw007

I am trying to build a database where I enter customer info such as,
name address, etc.
Then I can add items that they purchase including model number,
manufacturer and serial number.

I don't want it to act like an invoice program. I use Quickbooks for
that. Instead this will be a system that will allow me to go back
after maybe 90 days and generate a report that shows all of thier
components with serial number so that I can sell them an extended
warranty. We are a consumer electronics company, in case you were
wondering.

I already created a table with a uniqeu customer number for each
customer along with thier address, phone and other contact info.
That's as far as I got. I can't figure out how to link the other
database(s) that will store their items & s/n

Thanks in advance.
jason
 
You don't need another database. Create a new table for parts. Create a new
table for records (a "main" table).

Whenever you sell a part, you create a new record in the main table.

The main table should have an autonumber ID, a client number (I use lookups
for the data type, but others don't like that--I've never had a problem), a
part number (again a lookup to the parts table). You'll probably want date
purchased, etc.

When you do your query upon which you base your report, you'll want to use
all the tables.

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
 
So I have 3 tables...1 for customers, 1 for equipment and 1 for
records.
I enter samsung model HLN437 DLP TV in equipment table.

Then Sally Brown purchases that TV so the record table will pull her
name and info, that model tv, then it will add the serial number
specific to her order.
Sounds good...I'll try it.
Keep watching :)

Jason
 
I need a little more help, please.

I set up the tables and had everything working then I went a bit
further and got stuck.

I have the customer table, the products table which shows the
manufacturer, model and desc., Finally a records table which looks up
to the customer and adds a drop down box to choose the customer's last
name, then a product lookup that acts the same, then a space to enter
a serial number for each item.

Everything worked well.
Then I added a table for manufacturer and linked it to the products so
I did not have to retype PIONEER for each pioneer item. That also
worked until I tried to edit the records. I still get the drop down
box to show the product, but now it has the manufacturerID instead of
the ManufacturerName. So I get a drop down box showing a number and
the model. I can use it that way except I have to remember what
manufacturer is what number. It's kinda a pain. I tried every possible
relationship and lookup combo and could not make it work.
Does that need to be 2 fields instead of 1 product field?

Any help would be great.
Thanks

Jason
 
Ok. When you made your Mfr table, did you then put a lookup in the products
table to the mfr table? That makes sense (NOT in the records table). Then,
the records table will still have what you had before, no Mfr lookup 'cause
you're hooked into the Mfr through the product chosen. Also, you don't want
to have the SN with the product, huh? You want that specific to the customer
only? That makes sense if the items are NOT resold, OR if you don't track
the reselling of them.

I'll keep checking back. :)
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
 
Yes, the lookup is in the products table to look up the
manufacturerfrom the manufacturer table.

It worked the way I had it, it was just hard to read. SO now I added
another field to the records table where I pick the manufacturer from
a list. It works good.

I do not want to track the resale of the equipment. Electronics are
like computers. they are worthless in a year.

Thanks for your help. I think I have it all set up.
Jason
 
Back
Top