SQL Problem?

Hey everyone...

Right now, when my form1 loads it fills a datagrid with the top 10 records of from a table called CUSTOMERS.

Then I have it so I can press Shift + Enter to Update the selected record in the datagrid. So then it opens a new form and displays the information in individual textboxes for updating.

Here is what I need to do...

When the User Presses just the "Enter" key on form1's datagrid...it then needs to open another form which contains all INVOICES that correspond with the selected Record. I want to fill another datagrid with the invoices!


Here is my DB design.

Table name - CUSTOMERS
Field Names - CUS_NUMBER, CUS_FIRST, CUS_LAST, CUS_PHONE
(there is some more but they are not relevant for this part)

Table name - INVOICES
Field Names - INV_NUMBER, INV_DATE, INV_AMOUNT, INV_PAID


Thanks for the help in advance!

How are you linking the two tables?

Right now I have no relationship between the tables. I wasn't sure of the best/right way to do this, so that's why I asked for help. So any suggestions is appreciated.

Thanks Again

If you want to display the invoices for a selected customer, you have to have a foreign key in the invoices table (CUS_NUMBER will do). This CUS_NUMBER foreign key will link the 2 tables:

CUSTOMERS
CUS_NUMBER, CUS_FIRST, CUS_LAST, CUS_PHONE

INVOICES
INV_NUMBER, CUS_NUMBER INV_DATE, INV_AMOUNT, INV_PAID

In the end it all depends on your db design: E.g.

A customer can have one/many invoices.
Invoices made up of one/many items, etc.

It gets even better/worse:
You also have to normalise (which is a whole topic on its own) your tables.

So since I have all my CUS_NUMBER's in the Customers table, do I just have to copy and paste them into the Invoices table into the INV_CUS_NUMBER field that I set up as a FK?

Once I create the FK....then when I add a new customer to the Customer's table...will it automatically put the Customers Number in the INV_CUS_NUMBER field in the Invoices table?

And what do you mean when you say "Normalise"?

Thanks for the help

Hi,

You will only add the customer to the invoive once he/she makes a transaction.

You can view your customer table as the master table and your invoices as the transaction.

Normalisation: If you are doing a db project and you do not know the normalisation procedure you can get into a real nightmare with your project. I will strongly urge you to get a good book on database design, as it can be a really involved process.