need help building a new db

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I only know the very basics of Access and I need some help doing a project
at work. I will try and describe the project as best I can.
Basically, I need to take invoices we get,and enter in the detail behind the
invoice and produce a topsheet that totals out from the detail for our
accounting department. So for instance, one invoice comes in and it has lets
say 6 categories of detail. In each category, there are multiple (upwards of
40 sometimes) dollar amounts. How can I create a table and then a form in
which I can enter in all of the detail and tie it to the one invoice number?
We used to do all of this by Excel, but would like to be able run reports to
help us budget based on the past invoices. Any help you could give would be
greatly appreciated!
Thanks
Jason
 
Hi
I only know the very basics of Access and I need some help doing a project
at work. I will try and describe the project as best I can.
Basically, I need to take invoices we get,and enter in the detail behind the
invoice and produce a topsheet that totals out from the detail for our
accounting department. So for instance, one invoice comes in and it has lets
say 6 categories of detail. In each category, there are multiple (upwards of
40 sometimes) dollar amounts. How can I create a table and then a form in
which I can enter in all of the detail and tie it to the one invoice number?
We used to do all of this by Excel, but would like to be able run reports to
help us budget based on the past invoices. Any help you could give would be
greatly appreciated!
Thanks
Jason
Click to expand...

Take a look at the Orders application in the Northwind sample database
on your Access install disk; it has the rudiments of just what you
want to do.

In short, you would have at least two tables: a table of Invoices,
related one to many to a table of InvoiceDetails. This table would
have an InvoiceId field as a link to Invoices, and could contain 40 or
400 (or 4,000,000 if you insist!) records per invoice. You could enter
the data using a Form based on the invoice table, and a Subform based
on InvoiceDetails. Subtotals, totals, etc. should not be stored in
tables but should be calculated "on the fly" on a Report or in a
totals query.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks John! That was exactly what I needed!
Now I have a more detailed question: How could I control which categories of
Invoice Detail show up when I enter in the invoice type (I am using an
InvoiceID to relate the tables, but I also have several types of Invoices
which have different variations of detail needed)? Should I use queries?
Thanks for your help!
-Jason
 
Thanks John! That was exactly what I needed!
Now I have a more detailed question: How could I control which categories of
Invoice Detail show up when I enter in the invoice type (I am using an
InvoiceID to relate the tables, but I also have several types of Invoices
which have different variations of detail needed)? Should I use queries?
Thanks for your help!
-Jason
Click to expand...

Without knowing about the structure of your tables, I can't be sure -
but yes, you can base Combo Boxes on Queries, and the query can
reference a control on your form. If you have a combo box on
frmInvoice named cboInvoiceType, you can base the Category combo box
cboCategory on the subform on a query using

=[Forms]![frmInvoice]![cboInvoiceType]

as a criterion on the appropriate field in the Categories table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top