Autofill information in a table (1 Viewer)

I essentially created an account since I can't seem to find a straightforward answer much anywhere else (plenty of hints on auto-filling forms- but that isn't particularly conducive to my specific need on this).

I've attached a blank copy of my database.

I may need some help overall- but the immediate concern I'm having is that I want to be able to autofill data in the invoice table based on the customer table and the products/service table.

*I want to be able to select a first name [or other primary key if necessary] to fill in the last name, company, address, city/state/zip, phone, fax, cell/alt phone, and email automatically... that is without multiple dropdown selections or input to those sections at all.

*I want prices to fill in to the 'cost of product/service X' so that I may use it for other calculations in the invoice table- as well as to make forms from it directly.

I haven't had the most luck with getting an answer- let alone any assistance, and am hoping that I'll have more luck here. It's proving to be a much larger undertaking than expected to do these relatively small tasks for me, and I just want to move on to getting it up and running.

I want to be able to autofill data in the invoice table based on the customer table

Click to expand...

That's not how databases work. You don't store redundant data all over the place, you create queries and link your datasources and get your data that way.

I can't open your Invoice table, so I can't see what fields you have in there. I do however know which fields you should have to accomplish what you want--CustomerID. That is the only field from Customer that you need in Invoice. With that you can link the two in a query and get all the data from both tables.

Additionally, I see these issues with your tables:

~Non alphanumeric characters. In table and field names you should only use letters, numbers and underscores. When you add special characters (#, /, spaces) it makes coding and writing queries more difficult. I'd eliminate them before you go further.


~No autonumber primary keys. Each table should have a primary key that is an autonumber field. Then when you need to link tables (like Customer to Invoice) you would use that field as the foreign key. You do have some primary keys in some tables, but I would really advise using autonumbers instead.

plog said:

That's not how databases work. You don't store redundant data all over the place, you create queries and link your datasources and get your data that way.

I can't open your Invoice table, so I can't see what fields you have in there. I do however know which fields you should have to accomplish what you want--CustomerID. That is the only field from Customer that you need in Invoice. With that you can link the two in a query and get all the data from both tables.

Additionally, I see these issues with your tables:

~Non alphanumeric characters. In table and field names you should only use letters, numbers and underscores. When you add special characters (#, /, spaces) it makes coding and writing queries more difficult. I'd eliminate them before you go further.


~No autonumber primary keys. Each table should have a primary key that is an autonumber field. Then when you need to link tables (like Customer to Invoice) you would use that field as the foreign key. You do have some primary keys in some tables, but I would really advise using autonumbers instead.

Click to expand...

Thanks. A lot of my problem stems back to revisiting some basics. But while I'm re-watching some old tutorials, I do have some more specific questions that I'm attempting to answer along the way.

Attached is the edited version of what I have (information outside of pricing is gutted, of course). What I've seen on query tables so far is only really applicable to static information. I'm trying to reuse mine repeatedly in order to create several invoice forms.

I'm attempting to get some direction in the mean ><

Suggest that this model may give you some ideas re relationships, fields etc.

I don't think Employee FirstName and Position is a good choice for PK.

jdraw said:

Suggest that this model may give you some ideas re relationships, fields etc.

I don't think Employee FirstName and Position is a good choice for PK.

Click to expand...

Actually yes, that does make abstracting it a bit easier, but it still leaves me with a question or two after the fact.

Since I can't exactly use static prices as a unique indexed value (multiple items of varying prices- some of which are the same), I'm at a bit of a loss on how to go about creating a solid invoice table with foreign keys from the products table. Specifically- getting a price onto an invoice without manually typing it in.

The actual problem is that I don't want users to have the level of freedom to let them type in (potentially arbitrary) prices- is there a way to enforce the creation of an invoice with a set form of 'item cost' field? From then I can easily perform operations on the set number with (perhaps) a percentage discount table, sub totals, and tax calculations all as built in functions.

Or am I still approaching this from the wrong angle?

You put the AgreedUpon Price for the Item, and the Qty of Item(s) purchased on the Order detail. If you don't, and you rely on the Item Price from the Product table, then when you change the Price of the Product in the Product table--ALL of your history records will change because of that new Price.

Better to use an AgreedUponPrice and Qty in OrderDetail. This allows for discounts, sales, loyalty programs,..etc. You could always copy the value from the Product table as a starting value that could be modified, if that makes your processing easier--
BUT you record the value in the Agreed price.
Good luck

jdraw said:

You put the AgreedUpon Price for the Item, and the Qty of Item(s) purchased on the Order detail. If you don't, and you rely on the Item Price from the Product table, then when you change the Price of the Product in the Product table--ALL of your history records will change because of that new Price.

Better to use an AgreedUponPrice and Qty in OrderDetail. This allows for discounts, sales, loyalty programs,..etc. You could always copy the value from the Product table as a starting value that could be modified, if that makes your processing easier--
BUT you record the value in the Agreed price.
Good luck

Click to expand...

Sensible- I suppose it'll be something that I have to live with. I've definitely gotten a lot of useful information over the past few days, however.

Any other questions I foresee- concerning forms especially- escape the scope of the thread. Forms are largely foreign to me altogether- although, I think I'll dive in headfirst and try to figure out what I can before simply asking for answers on that.

Thank you for the help

Sensible- I suppose it'll be something that I have to live with. I've definitely gotten a lot of useful information over the past few days, however.

Click to expand...

You don't have to live with anything when you know it will happen and you know how to avoid it. This is a design issue. You have a given fact. It's your decision to live with it or correct it. Easier to change it at design time than downstream.