If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Preliminary design
I am in the first stage of creating a DB that will be utilized by six people.
They will be keying data and running queries during each month as invoices are paid or services performed and invoices expected to be updated later in the month. In the design i understand there needs to be a table for customers then another for invoices yet each invoice has an itemized description of charges. Should there be another table for these charges or are these fields in the invoice table? There is also an internal expense account that is applied to the invoice that identifies type of expense. The main purpose of DB is to track expenses so any advise is appreciated. Thanks |
#2
|
|||
|
|||
Preliminary design
This has to be driven by the specifics of the informaiton that you are trying
to database, and by the details of what you are trying to accomplish. So far you've given us only three sentences (1/2 of your one paragraph post) on that, so we only know a little. Taking your question literally and at face value, I think that you need an InvoiceItems table, linked to your invoice table. I suspect that once you really think this through, it's going to get complicated. Like that you will find that the entities that you are really tracking are delivery of billable services and payment for them. And that "invoicing" is a one time transmittal/notification action to a customer, to be followed by different types of actions (sending of statements) when you don't receive payments. |
#3
|
|||
|
|||
Preliminary design
Thanks. As stated it is a expenses tracking database so for example in
describing the relationships, One invoice can have many description of charges so yes it would probably be an invoice items table tied to an invoice table though not sure what the primary key would be. I then would have an expense account table with account number and acct description as fields in this table. In this relationship it would be an invoice can only have one expense account so i assume this would be a one to one relationship from invoice table to account table. Is this correct? "Fred" wrote: This has to be driven by the specifics of the informaiton that you are trying to database, and by the details of what you are trying to accomplish. So far you've given us only three sentences (1/2 of your one paragraph post) on that, so we only know a little. Taking your question literally and at face value, I think that you need an InvoiceItems table, linked to your invoice table. I suspect that once you really think this through, it's going to get complicated. Like that you will find that the entities that you are really tracking are delivery of billable services and payment for them. And that "invoicing" is a one time transmittal/notification action to a customer, to be followed by different types of actions (sending of statements) when you don't receive payments. |
#4
|
|||
|
|||
Preliminary design
If this is to be a multi-user system related to services and invoicing, is
there a reason you aren't using a commercially-available shrink-wrap package? Recreating the functionality of something like, say, Quickbooks in Access is not for the faint of heart...g Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I am in the first stage of creating a DB that will be utilized by six people. They will be keying data and running queries during each month as invoices are paid or services performed and invoices expected to be updated later in the month. In the design i understand there needs to be a table for customers then another for invoices yet each invoice has an itemized description of charges. Should there be another table for these charges or are these fields in the invoice table? There is also an internal expense account that is applied to the invoice that identifies type of expense. The main purpose of DB is to track expenses so any advise is appreciated. Thanks |
#5
|
|||
|
|||
Preliminary design
Thanks Jeff. This database will be replacing the use of workbooks since it
really is for the purpose of logging expenses and keeping track. In the month end there is some filtering of all data and sum totals but not much more than that. There is a need for a simple dtabase file format than excell flat file. The invoices are not generated but received and information is recorded. "Jeff Boyce" wrote: If this is to be a multi-user system related to services and invoicing, is there a reason you aren't using a commercially-available shrink-wrap package? Recreating the functionality of something like, say, Quickbooks in Access is not for the faint of heart...g Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I am in the first stage of creating a DB that will be utilized by six people. They will be keying data and running queries during each month as invoices are paid or services performed and invoices expected to be updated later in the month. In the design i understand there needs to be a table for customers then another for invoices yet each invoice has an itemized description of charges. Should there be another table for these charges or are these fields in the invoice table? There is also an internal expense account that is applied to the invoice that identifies type of expense. The main purpose of DB is to track expenses so any advise is appreciated. Thanks |
#6
|
|||
|
|||
Preliminary design
Hello ACSS
Would like to help but the important basics are still unclear. You use terms ("invoice", "services performed" "expense", "updating" of invoice, "customer") with important you-specific definitions that you've not told us about. I can't even tell whether you are buying or selling, as your post seems to conflict with itself on that. You are calling these "expenses" (= buying) yet are talking about customers and services performed (= selling). But, without knowing all of that, here's a guess in the dark on your structure question: (of course feel free to change/shorten my overly long explanatory field names) Table Customers: Record = an entity that these invoice transactions are with PK (Primary key) = CustomerIDNumber Plus customer descriptive fields Table: ExpenseCategories Record = Standardized expense category PK = ExpenseCategoryIDNumber plus category descriptive fields Table: Invoices Record = Invoice "header" type informaiotn PK = InvoiceIDNumber FK (Foreign Key) ExpenseCategoryIDNumber FK CustomerIDNumber plus fields for "one per invoice" type information Table: InvoiceItems Record = Invoice items FK = InvoiceIDNumber PK (unused) InvoiceItemsIDNumber Link all fields to their namesakes Wherever you don't already have real worlding numbersystems in place for the keys, make the PK fields autonumber types, and FK fields long integer types Hope that helps a little! |
#7
|
|||
|
|||
Preliminary design
To try and povide some clarity.....we receive invoices from outside
contractors and pay for their services performed. The submit invoices and their is a break down (description of their expenses such as fuel, import tax, overtime..etc) Each invoice has a unique ID number and never repeated, contractor name, address, etc. This invoice is paid from us and we assign an expense account that has a internal description for our purposes. The table you listed as Expense Categories could be the expense account and description that is applied to the invoice for payment. Does this help?? "Fred" wrote: Hello ACSS Would like to help but the important basics are still unclear. You use terms ("invoice", "services performed" "expense", "updating" of invoice, "customer") with important you-specific definitions that you've not told us about. I can't even tell whether you are buying or selling, as your post seems to conflict with itself on that. You are calling these "expenses" (= buying) yet are talking about customers and services performed (= selling). But, without knowing all of that, here's a guess in the dark on your structure question: (of course feel free to change/shorten my overly long explanatory field names) Table Customers: Record = an entity that these invoice transactions are with PK (Primary key) = CustomerIDNumber Plus customer descriptive fields Table: ExpenseCategories Record = Standardized expense category PK = ExpenseCategoryIDNumber plus category descriptive fields Table: Invoices Record = Invoice "header" type informaiotn PK = InvoiceIDNumber FK (Foreign Key) ExpenseCategoryIDNumber FK CustomerIDNumber plus fields for "one per invoice" type information Table: InvoiceItems Record = Invoice items FK = InvoiceIDNumber PK (unused) InvoiceItemsIDNumber Link all fields to their namesakes Wherever you don't already have real worlding numbersystems in place for the keys, make the PK fields autonumber types, and FK fields long integer types Hope that helps a little! |
#8
|
|||
|
|||
Preliminary design
Hello Acss,
Yes, that clears up all of the big questions and helps a great deal. Based on that I think that my guessed structure wouuld be what I would recommend as a starting point. . To discuss a couple of fine points: My structure reflected your statement which was, in essence, that a single expense sccount number would always apply to the entire invoice. I'd double check that; if so, fine (and less work)......if not, move the FK ExpenseCategoryIDNumber field from the "Invoices" table to the "InvoiceItems" table, in which case you'll be assigning expense number to the individual items. Another decision is whether to make the Invoice Number be just a field or make it the primary key (in place of my "InvoiceIDNumber" in those last 2 tables) Long story short, my recommendation would be to make it just another field (VendorsInvoiceNumber) (shorten that) Looks like you're on your way! |
#9
|
|||
|
|||
Preliminary design
Thanks Fred. There can only be 1 expense account applied to each invoice
regardless of how many individual items on invoice. There fore i expect one table that has two fields (accountnumber) & (account description) with account number being PK. In the invoice table i suspect InvoiceID to be the unique field and it would be PK. Once i move along and create fields relationships i can post later to see if i am on the right track. Thanks again "Fred" wrote: Hello Acss, Yes, that clears up all of the big questions and helps a great deal. Based on that I think that my guessed structure wouuld be what I would recommend as a starting point. . To discuss a couple of fine points: My structure reflected your statement which was, in essence, that a single expense sccount number would always apply to the entire invoice. I'd double check that; if so, fine (and less work)......if not, move the FK ExpenseCategoryIDNumber field from the "Invoices" table to the "InvoiceItems" table, in which case you'll be assigning expense number to the individual items. Another decision is whether to make the Invoice Number be just a field or make it the primary key (in place of my "InvoiceIDNumber" in those last 2 tables) Long story short, my recommendation would be to make it just another field (VendorsInvoiceNumber) (shorten that) Looks like you're on your way! |
Thread Tools | |
Display Modes | |
|
|