SQL Problem?

[Neumee]

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!

[tpfkanep]

How are you linking the two tables?

[Neumee]

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

[tpfkanep]

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.

[Neumee]

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

[tpfkanep]

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.