Access 2000 Relationships

Hey all,

I'm new to the board and am in desperate need of help. I'm working on writing some budget software for my office, nothing fancy just something to check invoices, VISA transactions, etc. So my plan is to store the information into an Access database and then query the database inside of Excel to compare with our planned budget to we now have spent, that way knowing how much to spend and where. Anyway, my biggest problem lays in the database (no kidding).

Here is how I "want it" to work. You open the database starting with the first invoice or whatever we have an invoice number as the primary key, now that invoice number has any number of items in it (for example printers computer parts) so I want to relate each item to the one invoice transaction. What I did is made two tables one has the invoice number as the primary key and relevant information about the invoice, the other table contains information about items on a certain invoice.

Invoices relate to items in a one-to-many relations one invoice can have any number of items. However I cannot get the Forms to realize that. The way I need the forms to look is that you see the invoice and its info, then click a button to view/add the items on the transaction. But you should ONLY see items that pertain to that unique invoice.

If someone could point me in the right direction here, I'm losing my mind. Maybe there is a easier way to do this that I don't know about, but and help would really be appreciated.

-Robert

Sounds like you've almost got it right first go. Learning DB relationships is one of the trickiest things I've ever had to do.

Your DB setup is pretty close to being correct. Here's what I would do.

table: invoices
invoiceid, auto incrementing int (PK)
datecreate, datatime
...etc - other invoice specific stuff

table: products
productid, int (PK)
description, varchar
price, money

table: invoice_products
productid, int (FK)
invoiceid, int (FK)
qty, int

See how I've got a table called invoice_products. This table is called the bridging table, as it is a bridge between your products and invoices. This way, you don't have to repeat all your product details in every invoice you create (removing redundant data is a major part of creating DB's).

In order to show this on the form, I'd use a list box. You're query to the db would be something like,

Hey stumpy,

Thanks for the help, but I do have a few questions:

If I added quantity to the products table wouldn't there be no need fo the invoice_products table?

The way I have the tables set up now are as follows:

Table : invoice
invoiceid, number , pk
date , date/time
etc....

Table : invoice_details
detailsid, autonumber, pk
invoiceid, number, fk
description, text
etc....

I was thinking, when I set up the form in Access that the invoice form would have a button to open invoice_details from with invoiceid field matching. But this doesn't seem to work.

I usually get various relationship errors.

Maybe a lookup is what I need, so I could lookup what products have the same invoiceid and only view those.

Thanks again for your help,

Robert

You have to have a products table. Otherwise, you'll have your product names, details, prices, etc, repeated all the way through your invoice_details table - which is redundant. Also, where else are you going to get your product info from?

Basically, your products table is the lookup. You simply grab the ProductID from it, and insert it into your invoice_details table.

Designing DB's isn't about trying to get as few tables as possible. It's about normalisation [google it]. That is, the process of breaking down groups of data so that there is no repetition, and so that your DB is easily maintained.

I assume, that your master table is "Invoices", (the one side).
transaction table is "Details", (the many side).

Go to Tools->Relationships on the Access 2000 menu bar, and create 1-many relationship between these tables, select "Enforce referential integrity" then save the relationship.

Create a new form for master table, "Invoices". You should use a columnar view in the design, for this simply use the form wizard. Save the form with name "frmInvoices". Keep it open in design view.

Use Form wizard to create a new form for details table. Selece tabular form for display. Enlarge the form to accomodate display of around 5-10 records at a time. Save it and name it "frmDetails". Close it also.

You have two form now:
1. frmInvoices (the master form)
2. frmDetails (the child form, soon it will become a child of "frmInvoices" :-)

Be careful to follw these steps now:-

Now go to database window. Drag the form "frmDetails" to the "Details" area of the form "frmInvoices", (remember I asked to keep it open in design view, if you've close it re-open it in design view). Make proper positioning and resizing of both forms, save it (it will be saved as "frmInvoices") and you're done.

Open "frmInvoices" in form view. Related records will be displayed from "Details" table as you flip the records on the "Invoices" table. You can Add/Edit or Delete Records from both tables here.

John