Need help with invoice model

[lolner]

Hello everyone!



I'm trying to develop a Access invoice substitute for my actual excel invoice model.

But i'm having problem with the logic behind it, so i need some help with it.

We build and manufacture custom computers, so our products have a lot of variable components on it.

I'm wanting to have a invoice model where i have:

-Invoice number
-Customer name
-Data

-Product (computer model X)
-Processor (combo list chosen from products tables)
-Graphics card (same)
-RAM (same)
-Storage 1 (same)
-Storage 2 (same)
-Motherboard (same)
-Operational System (same)

and goes on...

But i didnt find a way to store these choices in a table (so i can access the invoice order xxx from customer yyy, that i choose the xxx processor and yyy graphics card).


My actual excel is in this way:

Component Value Quantity Subtotal
Computer XXX XXX.XX (sum of components hidden value) 1 XXX.XX (component x quantity)
Component name 1: (dropdown list) (dont show the value) 1
Component name 2: (dropdown list) (dont show the value) 1
Component name 3: (dropdown list) (dont show the value) 1
Component name 4: (dropdown list) (dont show the value) 1
Component name 5: (dropdown list) (dont show the value) 1
Monitor: (dropdown list) XXX.XX 1 XXX.XX (component x quantity)
Keyboard and mouse: (dropdown list) XXX.XX 1 XXX.XX (component x quantity)


At this moment i'm doing like this way:




My actual excel model is this (the red number are the hidden values):



Also i make a auto datasheet of the computer (it takes proprieties of the components) automatically, is there any way i can do this (i use dlookup on excel, the best way to do this is using dlookup on access too?)





Any help would be good!

Thanks in advance

[June7]

Conventional structure would be an Invoices table and a related table for InvoiceItems.

No, DLookup is not best approach. Build queries that join related tables. Or a report with subreports.

Recommend you complete an introductory tutorial book on Access.

[Micron]

You would also need a table for every option type in one field and the option value in another field, or a more normalized fashion where there is a separate table for each option type. I mention this because you seem to want to use combos to limit option choices to the ones in a list (I think that's a good idea). Then there is the report (or reports) that you'd need. Access is the best tool for this job, but it's much easier to make a mess of a db than a spreadsheet IMHO, and more labour intensive to fix or muddle along with as well, when it's done badly.

I see your document has a lot of images. Don't store them in your table(s). There are better ways.