Access Help?

[Mars]

Ok So Ive been through Lynda.com's essential access course and I went through O'Rileys Missing Manual and if anyone wants me to make a database for their CD collection I can say I'v got that down. I will make a kickass Bobblehead database for you also. Including some funky things like a "Search by Genre" button right on your switchboard for you with thousands of the little bobbers. All of this is useless however, if you want to keep a record of invoices with their different payment methods. Or if you wanted to add up the stock that went out on those invoices. :erm:

So despite my ass kicking ability with Bobblehead/CD collections, I need real help.
This seems to be quite scarce. I have found a large amount of online resources for excel, most of them free, but for access its limited. I've had a look on VTC and although its a longer course it seems to be pretty much the same as Lynda.com's one.

Ive been tinkering with the Northwind template, and while the results are promising I still kinda have no idea how to go forward. I don't want to make invoices, I want to keep a record of them, so I don't want a big form for it. what I want is a table of columns like this:

Invoice No | Account No | Payment Method | Value || Stock Item | QTY | Stock Item | QTY |

The |Stock Item | QTY | would be variable since some invoices would have one stock item and others many.
Then the stock items would be taken off the stock sheet, and the different payment methods would be added up per day.

There is more. Where can I get help on this kind of thing. I am willing to pay a reasonable price.
Does anyone know of a site or company offering this kind of thing?

 

[graviti]

Best Option is actually two tables.

One with the following

INVOICE DETAILS

Invoice No | Account No | Payment Method | Value |

INVOICE ITEMS

Invoice No | Stock Item | QTY |

The two 'Invoice No' keys are linked. So you can have one invoice with basic details, as follows

INVOICE DETAILS

Invoice No | Account No | Payment Method | Value |
0120218 439981 EFT R1298.07

INVOICE ITEMS

Invoice No | Stock Item | QTY |
0120218 217 5
0120218 229 6
0120218 198 1

Then you can just use some intelligent SQL to generate the necessary details.

Just the way I would do it. Hope that gives you a kick in the right direction.

 

[Mars]

ok. I think I understand..
I'm going to give it a go.

 

[Borrels]

Best Option is actually two tables.

One with the following

INVOICE DETAILS

Invoice No | Account No | Payment Method | Value |

INVOICE ITEMS

Invoice No | Stock Item | QTY |

The two 'Invoice No' keys are linked. So you can have one invoice with basic details, as follows

INVOICE DETAILS

Invoice No | Account No | Payment Method | Value |
0120218 439981 EFT R1298.07

INVOICE ITEMS

Invoice No | Stock Item | QTY |
0120218 217 5
0120218 229 6
0120218 198 1

Then you can just use some intelligent SQL to generate the necessary details.

Just the way I would do it. Hope that gives you a kick in the right direction.

Agree. For designing the structure of a database table, I find it helps to think of it in terms of physical items, e.g. invoices and stock items, and defining table accordingly. And if you are interested in establishing the link between these physical items you should create a relationship between the table, similar to the relationship on the physical item, e.g. each invoice has one or more stock items on it.

Maybe check out some advice on designing relationship databases such as http://databases.about.com/od/specificproducts/Database_Design.htm

 

[SirFooK'nG]

I would do the same thing. Two tables as described. VBA is your best bud in these cases. Yes is oldschool, but kickass for a really good Access dB.

i.e Every time you add a new record (Invoice) you can run a query or some vba on the add record event or update event to update the stocklist. Not to complicated.