bit of advice on tables?

[w0od0o]

i have created a db with 2 tables, first is clients second is invoices, i have got to a stage where i can select a client from a list which then adds all the client data to another form with the invoice fields/boxes on,

my question is.....is there a way of getting all that data into the invoices table or another table rather than having the data split?

thanks

w0od0o

 

[RuralGuy]

You *want* the data split! All you need in the Invoice table is the Client ID which is hopefully an AutoNumber. Then all of the data from the Client table is available to the Invoice table using a Join in a query.

 

[Preyzar]

i have created a db with 2 tables, first is clients second is invoices, i have got to a stage where i can select a client from a list which then adds all the client data to another form with the invoice fields/boxes on,

my question is.....is there a way of getting all that data into the invoices table or another table rather than having the data split?

thanks

w0od0o

If I'm correct in assuming what you really want to see is all the info showing up so you can create an invoice using Reports, you would combine the data you want in a query, not in a table.

Properly normalized tables shouldn't have duplicated data which would be inefficient and redundant.

 

[w0od0o]

yes a query to create the report would be fine but if i wanted to recall an invoice how would the invoice data know what client i assigned to that invoice if the client data is seperate?

 

[RuralGuy]

As stated earlier: the Invoice table needs a ForeignKey field that contains the PrimaryKey of the Client table.

 

[w0od0o]

sorry really new to this how do i set a foreignkey?

 

[w0od0o]

do i creat a relationship between the 2 tables and the column names?

also what would the datatype be for the foreignKey?

 

[RuralGuy]

do i creat a relationship between the 2 tables and the column names?

also what would the datatype be for the foreignKey?

The datatype of the ForeignKey field *most* be the same as the PrimaryKey field in the other table. If the PrimaryKey is an AutoNumber field (which I hope it is) then make it a Long Integer numeric field. ForeignKey is simply a name we give fields in tables that contain KeyFields from other tables. If done correctly, you can join the two tables on this ForeignKey field and set up a relationship as well. Getting the PrimaryKey value in the ForeignKey table is a manual process unless you use SubForms where Access will do the work for you.

 

[RuralGuy]

Here's a link to a sample Database:
http://www.bullschmidt.com/access/default.asp
...and here's a link to a ton of Data Models:
http://www.databaseanswers.org/data_models/index.htm

 

[w0od0o]

got a bit further i think :S i know have the tables linked, my client table now has a expand button on it which drops down the list of invoices for each client,

i have changed my client selector list to pick from the client id in the invoices table which works fine but, click on client, opens another form then typr the data u want untill you come to do another invoice then it opens the same page but changes the client details but keeps the invoice data.....any help is appreciated

thanks

w0od0o

 

[RuralGuy]

What is the Datatype of the PrimaryKey in the Client table? What are you doing to add a new Client?

 

[w0od0o]

gone round in circles all day, i wanted to add new clients ect from another page, ive attached my db if you would mind having a look and hopefully you will see what im trying to do

thanks

w0od0o

 

[RuralGuy]

Lookup Fields just confuse the programmer! The Evils of Lookup Fields in Tables
Just change it to a TextBox and not a ComboBox. Now see if it is a little easier to see what is in the table.

 

[w0od0o]

if its a text box how will the user know what client to pick?

is there not a way of assigning the invoice ID through my "select client" combo box on a new page?

w0od0o

 

[RuralGuy]

if its a text box how will the user know what client to pick?

The form has a ComboBox for the lookup but *not* the table.

 

[RuralGuy]

Don't try and do too much with your Form/Subform combination. Just show a Client and their invoices. Create a new client on a different form.

 

[Simon_MT]

As a starter for 10, You need three tables 1) Clients 2) Invoice 3) Invoice Details.
You also need a Order File and a Contracts File and I presume a Product file

On the Invoice table:
Client (ID) Combi from Clients
Order (ID) Combi from Orders
Contract (ID) Combi from Contracts

On Invoice Details table
Invoice parsed from Invoice Table
Product Qty Value

No need to get to a position whereby you can search on a particular Client and see what Invoices have beeen issued.

This very simplified but I would suggest that use an real scenario and emulate it in your database.

Simon

 

[w0od0o]

not to sure if i need that much info to have 4 tables, its to invoice for an installations company which would issue invoices for the amount of hrs (invoice details) and charged at a set rate (Client details)

 

[w0od0o]

just changed the clientID - invoice table (foreign key) from a combo box to a text box and created a combo box on my form and now theres no data in the combo box (unless i fill in old invoices, then it will select clients i have used in the past), should the combo box on the form be linked from the clients table (clientID) or the Invoice table (clientID_inv)? if its the foreign key on the invoice table where does that get its data from?

i dont know if ive gone round in circles and changed something i shouldnt or if im doing it wrong lol

hope i havent confused anybody :S

w0od0o

 

[w0od0o]

just had a thought, should the data be coming from a query or directly from the table?