A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Invoicing database design



 
 
Thread Tools Display Modes
  #1  
Old November 4th, 2009, 02:45 PM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 20
Default Invoicing database design

I am designing a database that includes the simple (!) functionality
of producing invoices (not an accounting package). I understand the
convention is to have a table for invoice headers and one for invoice
transactions. My question is what are the advantages of this approach
rather than just having one table for invoice transactions. The
printed invoice (an Access report) can have the invoice headings
(date, invoice no customer address etc) in the report header and the
invoice transactions in the report detail. What would be wrong with
that?

Gordon
  #2  
Old November 4th, 2009, 03:21 PM posted to microsoft.public.access.tablesdbdesign
Klatuu[_2_]
external usenet poster
 
Posts: 53
Default Invoicing database design

"What would be wrong with that?"
Almost everything.
The common design of a header and transactions in separate but related
tables is always the correct way to accomplish this.

If you had one table to hold it all, how many individual transactions could
such a table hold? Well, with a limit of 256 fields in a table, you will
run out very quickly. And if you are saying to repeat the header info for
each transaction, then you are breaking the redundant data rule.

And in the report, where will the header info come from?

Or maybe I am missing the point and this post is a joke.
wrote in message
...
I am designing a database that includes the simple (!) functionality
of producing invoices (not an accounting package). I understand the
convention is to have a table for invoice headers and one for invoice
transactions. My question is what are the advantages of this approach
rather than just having one table for invoice transactions. The
printed invoice (an Access report) can have the invoice headings
(date, invoice no customer address etc) in the report header and the
invoice transactions in the report detail. What would be wrong with
that?

Gordon



  #3  
Old November 4th, 2009, 04:23 PM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 20
Default Invoicing database design

On 4 Nov, 14:21, "Klatuu" wrote:
"What would be wrong with that?"
Almost everything.
The common design of a header and transactions in separate but related
tables is always the correct way to accomplish this.

If you had one table to hold it all, how many individual transactions could
such a table hold? *Well, with a limit of 256 fields in a table, you will
run out very quickly. *And if you are saying to repeat the header info for
each transaction, then you are breaking the redundant data rule.

And in the report, where will the header info come from?

Or maybe I am missing the point and this post is a wrote in message

...



I am designing a database that includes the simple (!) *functionality
of *producing invoices (not an accounting package). *I understand the
convention is to have a table for invoice headers and one for invoice
transactions. *My question is what are the advantages of this approach
rather than just having one table for invoice transactions. *The
printed invoice (an Access report) can have the invoice headings
(date, invoice no customer address etc) in the report header and the
invoice transactions in the report detail. *What would be wrong with
that?


Gordon- Hide quoted text -


- Show quoted text -


Wow, I feel as though I have been flamed. This is no joke. It was
only a question on which I wanted advice. I said the invoicing
function was simple, not the database itself.

Fields in the one table invoicing transactions I envisage would be:

invoice date
invoice no
customerID
transaction date
transaction ref
productID
item description
Quantity
Unit price

As I see it, only the first 3 would break the redundant data rule.
The data for the invoice report (including the header) would be built
on the fly (using VBA) at time of invoice creation, drawing data not
just from the invoice transaction table but also the related tables on
customers/products etc.

But thanks for responding - at least you confirmed the only objection
to a one table design for invoice transactions is the data redundancy
rule which in this particular case is not a great overhead if it saves
having to maintain and update 2 tables.
  #4  
Old November 4th, 2009, 05:01 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Invoicing database design

Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. My understanding of
your proposed structure is different from Klatuu's, but in either case there
are literally no arguments in favor of your approach in a relational database.
It would make sense only in a flat database such as Excel. When a flat
database is called for Excel is generally the better tool for the job, but a
flat databse is not called for here.

Are you planning to copy the invoice number and other invoice header
information to each invoice item record? If so, is the only reason that you
believe it would be less overhead than maintaining two separate tables?

There are any number of objections to such an approach, one of which is that
overhead is likely to be greater, not less. You would not throw all of your
paper files into a large crate in the belief that a single container is less
overhead than a series of file drawers. The analogy is imprecise, but still
relevant.

Among the problems I see is that you do not have a ready way to view a single
invoice's information. One way would be to scroll through all of the records
until you notice the number you want, then take note of when the number
changes as you continue to scroll through the records. Another way would be
to filter the recordset (or load a selective recordset) to view a single
invoice. With a filtered recordset you would need to run the filter again to
view the next batch of records. With invoice header information in a single
table you could simply navigate to the next invoice to see all of that
invoice's associated line items. The flat file approach will inevitably lead
to a more awkward user interface.

Another objection would be the possibility of data entry errors. You could
avoid that to some extent by using code to copy the record, but that involves
overhead. Another is that you cannot enforce unique invoice numbers at the
table level. You could use code to check whether an invoice number has been
used for a different customer on a different date or something, but again
that is a lot of overhead, and probably difficult to construct.

"Only" three fields breaking the redundant data rule is three more than I
would accept. There is sometimes room for debate about what constitutes
redundant data, but not in this case. Adding a related table is easy, and
does not involve "maintenance" after it is created. Coding for a flat
structure is where the overhead really starts to add up.

wrote:
"What would be wrong with that?"
Almost everything.

[quoted text clipped - 25 lines]

- Show quoted text -


Wow, I feel as though I have been flamed. This is no joke. It was
only a question on which I wanted advice. I said the invoicing
function was simple, not the database itself.

Fields in the one table invoicing transactions I envisage would be:

invoice date
invoice no
customerID
transaction date
transaction ref
productID
item description
Quantity
Unit price

As I see it, only the first 3 would break the redundant data rule.
The data for the invoice report (including the header) would be built
on the fly (using VBA) at time of invoice creation, drawing data not
just from the invoice transaction table but also the related tables on
customers/products etc.

But thanks for responding - at least you confirmed the only objection
to a one table design for invoice transactions is the data redundancy
rule which in this particular case is not a great overhead if it saves
having to maintain and update 2 tables.


--
Message posted via
http://www.accessmonster.com

  #5  
Old November 5th, 2009, 01:27 PM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 20
Default Invoicing database design

On 4 Nov, 16:01, "BruceM via AccessMonster.com" u54429@uwe wrote:
Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. *My understanding of
your proposed structure is different from Klatuu's, but in either case there
are literally no arguments in favor of your approach in a relational database.
It would make sense only in a flat database such as Excel. *When a flat
database is called for Excel is generally the better tool for the job, but a
flat databse is not called for here.

Are you planning to copy the invoice number and other invoice header
information to each invoice item record? *If so, is the only reason that you
believe it would be less overhead than maintaining two separate tables?

There are any number of objections to such an approach, one of which is that
overhead is likely to be greater, not less. *You would not throw all of your
paper files into a large crate in the belief that a single container is less
overhead than a series of file drawers. *The analogy is imprecise, but still
relevant.

Among the problems I see is that you do not have a ready way to view a single
invoice's information. *One way would be to scroll through all of the records
until you notice the number you want, then take note of when the number
changes as you continue to scroll through the records. *Another way would be
to filter the recordset (or load a selective recordset) to view a single
invoice. *With a filtered recordset you would need to run the filter again to
view the next batch of records. With invoice header information in a single
table you could simply navigate to the next invoice to see all of that
invoice's associated line items. *The flat file approach will inevitably lead
to a more awkward user interface.

Another objection would be the possibility of data entry errors. *You could
avoid that to some extent by using code to copy the record, but that involves
overhead. *Another is that you cannot enforce unique invoice numbers at the
table level. *You could use code to check whether an invoice number has been
used for a different customer on a different date or something, but again
that is a lot of overhead, and probably difficult to construct.

"Only" three fields breaking the redundant data rule is three more than I
would accept. *There is sometimes room for debate about what constitutes
redundant data, but not in this case. *Adding a related table is easy, and
does not involve "maintenance" after it is created. *Coding for a flat
structure is where the overhead really starts to add up.





wrote:
"What would be wrong with that?"
Almost everything.

[quoted text clipped - 25 lines]


- Show quoted text -


Wow, I feel as though I have been flamed. *This is no joke. It was
only a question on which I wanted advice. I said the invoicing
function was simple, not the database itself.


Fields in the one table invoicing transactions I envisage would be:


invoice date
invoice no
customerID
transaction date
transaction ref
productID
item description
Quantity
Unit price


As I see it, only the first 3 would break the redundant data rule.
The data for the invoice report (including the header) would be built
on the fly (using VBA) at time of invoice creation, drawing data not
just from the invoice transaction table but also the related tables on
customers/products etc.


But thanks for responding - at least you confirmed the only objection
to a one table design for invoice transactions is the data redundancy
rule which in this particular case is not a great overhead if it saves
having to maintain and update 2 tables.


--
Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -


Hi Bruce, Thanks for your detailed post. You make a much more
convincing argument which has won me over. Part of my reticence to
construct an invoice header table and a separate one for invoice item
lines lay in my ignorance of the approach I should take. I did say I
was not designing an accounting system here but just the ability to
print invoices. Can you give me some advice?
I understand what fields should be in the 2 tables but am unclear how
to populate them.

The nature of the business is a service based one - charges are made
to a range of clients for each enquiry handled. These are captured
through a basic data entry screen, allocated to a client and populate
an enquiries transaction table. What is the best approach then to
populate the invoice header and invoice lines tables?

Gordon
  #6  
Old November 5th, 2009, 03:06 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Invoicing database design

Generally you would have something like the following:

tblInvoice (Invoice header table)
InvoiceID (primary key, or PK)
CustomerID
InvoiceDate
InitiatedBy
Other information specific to the invoice

tblDetails (line items or details for the invoice)
DetailID (PK)
InvoiceID
Item
UnitPrice
Quantity

InvoiceID needs to be the same data type (Number, Text) in both tables. If
InvoiceID is an autonumber in tblInvoice, it needs to be Number (Long Integer)
in tblDetails. Note that Number can be of various types. Long Integer,
Double, and Currency are the ones used most of the time.

Go to View Relationships. Add both tables. Drag InvoiceID from one table
to InvoiceID on the other. Click Enforce Referential Integrity when the
dialog appears. Note that the fields do not need to be the same name, but it
may be easier to keep track of that way.

Create a main form based on tblInvoice. From the toolbox, add a
subform/subreport control, just as you would add a text box, line, or anthing
else. With the subform control selected, click View Properties. Set its
Source Object to tblDetails, and its Link Child and Link Master fields to
InvoiceID if it is not already done. Click the three dots next to either
Link property, and it should take you through the rest of the process.

Add an Invoice record on the main form. On the subform, add a few line items
(details). Create another Invoice record (main form) with a few line items.
Go back to the first record. The line items should still be there. Look at
the tables. Each Detail record should have a value in InvoiceID
corresponding to an InvoiceID value in tblInvoice.

In many cases there is a listing of items that may be added to Details. If
you were dealing in products it would be a Products table. In the case of
services there may be a listing of services and prices. In this case you
would have a Services table:

tblService
ServiceID (PK
Description
UnitPrice
Quantity

tblDetails would look like this:

tblDetails (line items or details for the invoice)
DetailID (PK)
InvoiceID
ServiceID
UnitPrice
Quantity

Add tblService to the Relationship window, and create a relationship between
the ServiceID fields.

Base the subform on tblDetails as before. Rather than Item you will be
linking to tblService. Make a combo box. Set its Row Source to a query
based on tblService. The first column (hidden) in the combo box is ServiceID.
The other column(s) are visible, allowing you to select the item by name
rather than number. Set the combo box Control Source to ServiceID.

This is just a sketch, but I will have to leave it at that for now. For more
information about design and general database principles, this is a list John
Vinson often provides:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Crystal's tutorial is as good a place as any to start, IMHO.

To set up an incrementing Invoice number, one technique may be found he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=395


wrote:
Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. Â*My understanding of

[quoted text clipped - 76 lines]

- Show quoted text -


Hi Bruce, Thanks for your detailed post. You make a much more
convincing argument which has won me over. Part of my reticence to
construct an invoice header table and a separate one for invoice item
lines lay in my ignorance of the approach I should take. I did say I
was not designing an accounting system here but just the ability to
print invoices. Can you give me some advice?
I understand what fields should be in the 2 tables but am unclear how
to populate them.

The nature of the business is a service based one - charges are made
to a range of clients for each enquiry handled. These are captured
through a basic data entry screen, allocated to a client and populate
an enquiries transaction table. What is the best approach then to
populate the invoice header and invoice lines tables?

Gordon


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #7  
Old November 5th, 2009, 08:51 PM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 20
Default Invoicing database design

On Nov 5, 2:06*pm, "BruceM via AccessMonster.com" u54429@uwe wrote:
Generally you would have something like the following:

tblInvoice (Invoice header table)
* *InvoiceID (primary key, or PK)
* *CustomerID
* *InvoiceDate
* *InitiatedBy
* *Other information specific to the invoice

tblDetails (line items or details for the invoice)
* *DetailID (PK)
* *InvoiceID
* *Item
* *UnitPrice
* *Quantity

InvoiceID needs to be the same data type (Number, Text) in both tables. *If
InvoiceID is an autonumber in tblInvoice, it needs to be Number (Long Integer)
in tblDetails. *Note that Number can be of various types. *Long Integer,
Double, and Currency are the ones used most of the time.

Go to View Relationships. *Add both tables. *Drag InvoiceID from one table
to InvoiceID on the other. *Click Enforce Referential Integrity when the
dialog appears. *Note that the fields do not need to be the same name, but it
may be easier to keep track of that way.

Create a main form based on tblInvoice. *From the toolbox, add a
subform/subreport control, just as you would add a text box, line, or anthing
else. *With the subform control selected, click View Properties. *Set its
Source Object to tblDetails, and its Link Child and Link Master fields to
InvoiceID if it is not already done. *Click the three dots next to either
Link property, and it should take you through the rest of the process.

Add an Invoice record on the main form. *On the subform, add a few line items
(details). *Create another Invoice record (main form) with a few line items.
Go back to the first record. *The line items should still be there. *Look at
the tables. *Each Detail record should have a value in InvoiceID
corresponding to an InvoiceID value in tblInvoice.

In many cases there is a listing of items that may be added to Details. *If
you were dealing in products it would be a Products table. *In the case of
services there may be a listing of services and prices. *In this case you
would have a Services table:

tblService
* *ServiceID (PK
* *Description
* *UnitPrice
* *Quantity

tblDetails would look like this:

tblDetails (line items or details for the invoice)
* *DetailID (PK)
* *InvoiceID
* *ServiceID
* *UnitPrice
* *Quantity

Add tblService to the Relationship window, and create a relationship between
the ServiceID fields.

Base the subform on tblDetails as before. *Rather than Item you will be
linking to tblService. *Make a combo box. *Set its Row Source to a query
based on tblService. *The first column (hidden) in the combo box is ServiceID.
The other column(s) are visible, allowing you to select the item by name
rather than number. *Set the combo box Control Source to ServiceID.

This is just a sketch, but I will have to leave it at that for now. *For more
information about design and general database principles, this is a list John
Vinson often provides:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

Crystal's tutorial is as good a place as any to start, IMHO.

To set up an incrementing Invoice number, one technique may be found hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=395





wrote:
Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. *My understanding of

[quoted text clipped - 76 lines]


- Show quoted text -


Hi Bruce, *Thanks for your detailed post. *You make a much more
convincing argument which has won me over. *Part of my reticence to
construct an invoice header table and a separate one for invoice item
lines lay in my ignorance of the approach I should take. *I did say I
was not designing an accounting system here but just the ability to
print invoices. Can you give me some advice?
I understand what fields should be in the 2 tables but am unclear how
to populate them.


The nature of the business is *a service based one - charges are made
to a range of clients for each enquiry handled. *These are captured
through a basic data entry screen, allocated to a client and populate
an enquiries transaction table. *What is the best approach then to
populate the invoice header and invoice lines tables?


Gordon


--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...- Hide quoted text -

- Show quoted text -


Hey, thanks for taking the time to spell all that out for me. I
really appreciate that. I think I was looking for a different
solution, which reflected the work flow process. You see, all those
enquiries I talked about are entered through the month, then at the
end of the month I was looking to devise a routine that with the click
of a button would print out for each client an invoice (Access report)
listing the summary details of each charged for enquiry processed that
month with a unit price etc and a total invoice charge. The enquiries
table would then be updated with a flag (tick box) or the invoice no/
ID showing that the enquiry had been invoiced.

Perhaps that solution is a bridge too far and I should concentrate on
devising a main form/subform combination - combo box with clients in
the main form, enquiries relative to that client in the subform, with
a button to click to produce an individual invoice for that client for
all outstanding (ie uninvoiced) enquiries. In doing that I could put
some codein ther at the same time to write the relevant data to an
invoice header file and an invoice transactions file. Does that seem
feasible to you or can you think of a better approach?

Gordon
  #8  
Old November 5th, 2009, 09:34 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Invoicing database design

Are you saying there are invoices with several line items, and that there may
be several such invoices per month for a client? If so, are you further
saying that if there are three invoices per month for a customer, each
invoice with three line items, that you want the summary report to show all
nine line items, or that you want it to show three lines: the summaries of
the individual invoices?

Either may be done, but if you are summarizing an invoice into a single line
what detail information can you show other than grand total? You could add
up the total quantity, but that may not make sense if the three line items
are for different things.

It could be that the report's Sorting and Grouping capabilities could give
you what you need. However, I need to stress that if you have a conventional
invoice system with several line items grouped under a single invoice number,
you should proceed with related tables. If you are set up properly on that
first level any big picture data can be produced. This is true on every
level. If your design is properly normalized you can do almost anything with
the data.

I am struggling to convert a project from my earlier Access days that is not
properly normalized. On some things I knew better but decided it was too
much bother. On others I just didn't know better. Both are proving equally
vexing now.

wrote:
Generally you would have something like the following:

[quoted text clipped - 112 lines]

- Show quoted text -


Hey, thanks for taking the time to spell all that out for me. I
really appreciate that. I think I was looking for a different
solution, which reflected the work flow process. You see, all those
enquiries I talked about are entered through the month, then at the
end of the month I was looking to devise a routine that with the click
of a button would print out for each client an invoice (Access report)
listing the summary details of each charged for enquiry processed that
month with a unit price etc and a total invoice charge. The enquiries
table would then be updated with a flag (tick box) or the invoice no/
ID showing that the enquiry had been invoiced.

Perhaps that solution is a bridge too far and I should concentrate on
devising a main form/subform combination - combo box with clients in
the main form, enquiries relative to that client in the subform, with
a button to click to produce an individual invoice for that client for
all outstanding (ie uninvoiced) enquiries. In doing that I could put
some codein ther at the same time to write the relevant data to an
invoice header file and an invoice transactions file. Does that seem
feasible to you or can you think of a better approach?

Gordon


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #9  
Old November 6th, 2009, 10:35 AM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 20
Default Invoicing database design

On 5 Nov, 20:34, "BruceM via AccessMonster.com" u54429@uwe wrote:
Are you saying there are invoices with several line items, and that there may
be several such invoices per month for a client? *If so, are you further
saying that if there are three invoices per month for a customer, each
invoice with three line items, that you want the summary report to show all
nine line items, or that you want it to show three lines: *the summaries of
the individual invoices?

Either may be done, but if you are summarizing an invoice into a single line
what detail information can you show other than grand total? *You could add
up the total quantity, but that may not make sense if the three line items
are for different things.

It could be that the report's Sorting and Grouping capabilities could give
you what you need. *However, I need to stress that if you have a conventional
invoice system with several line items grouped under a single invoice number,
you should proceed with related tables. *If you are set up properly on that
first level any big picture data can be produced. *This is true on every
level. *If your design is properly normalized you can do almost anything with
the data.

I am struggling to convert a project from my earlier Access days that is not
properly normalized. *On some things I knew better but decided it was too
much bother. *On others I just didn't know better. *Both are proving equally
vexing now.





wrote:
Generally you would have something like the following:


[quoted text clipped - 112 lines]


- Show quoted text -


Hey, thanks for taking the time to spell all that out for me. *I
really appreciate that. *I think I was looking for a different
solution, which reflected the work flow process. *You see, all those
enquiries I talked about are entered through the month, then at the
end of the month I was looking to devise a routine that with the click
of a button would print out for each client an invoice (Access report)
listing the summary details of each charged for enquiry processed that
month with a unit price etc and a total invoice charge. *The enquiries
table would then be updated with a flag (tick box) or the invoice no/
ID showing that the enquiry had been invoiced.


Perhaps that solution is a bridge too far and I should concentrate on
devising a main form/subform combination - combo box with clients in
the main form, enquiries relative to that client in the subform, with
a button to click to produce an individual invoice for that client for
all outstanding (ie uninvoiced) enquiries. *In doing that I could put
some codein ther at the same time to write the relevant data to an
invoice header file and an invoice transactions file. *Does that seem
feasible to you or can you think of a better approach?


Gordon


--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...- Hide quoted text -

- Show quoted text -


Normally only 1 invoice per client per month - that invoice could have
one or more item lines (each one representing a unique enquiry with
its own characteristics). I have a table of those enquiries - what I
am struggling with is how do I convert the enquiries into invoice item
lines and invoice headers without going through each client and
creating an invoice header and then selecting the item lines to add to
that invoice or worse still re-keying the item lines (enquiries).
Unless you can suddenly see what I am driving at (which cannot be easy
I know), I will adopt the approach I outlined in my previous post
above.


Gordon
  #10  
Old November 6th, 2009, 01:43 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Invoicing database design

It sounds as if your term for line items is enquiry. It would help to know a
little more about the structure, but I will use part of the field list you
showed in an earlier post as a starting point. Work on a copy of the
database, of course.

invoice date
invoice no
customerID
productID
item description
Quantity
Unit price

I will assume InvoiceDate, InvoiceNo, and CustomerID are header items, and
the rest are line items, or enquiries. I will asume further that the
InvoiceDate is the same for all records with a particular InvoiceNo. Same
for CustomerID. You could make a SELECT DISTINCT query of InvoiceDate,
InvoiceNo, and CustomerID, or a totals query in which you group on those
three fields. In any case the query will include just the header fields, and
will show only unique records. Use the query to make a table containing the
same information. This could be a make-table query, or I think you can copy
a query and paste it as a table. There are other adaptations you could use,
but in the end there will be only one record per InvoiceNo.

Now make a copy of the original table. Delete InvoiceDate and CustomerID
fields. Create a one-to-many relationship between CustomerID in the Header
table created from the query and CustomerID in this new Details table. Use
the Header table as the basis for an Invoice form, and the Details table as
the basis for a subform as described in the posting in which I sketched the
table structure.

This should leave you with Header (Invoice) information on the main form and
related LineItem/Detail/Enquiry records on the subform.

Whatever the details, the general idea is that repeated Header information
will be consolidated into one table with no repetition, and Header fields
deleted from a copy of the original table to leave you with the Detail
information.

As for printing a report, you could filter or group the records as needed,
but I am not clear on how TransactionDate from your original posting of table
structure fits into this. I assume it is part of a Detail record, but I
don't know if the TransactionDate month will always be the same as the
InvoiceDate month. You can make it do what you want either way, but it is a
little more involved if the Header and Detail months are not the same in all
cases.

BTW, whenever I mention basing a form on a table you could (and probably
should) use a query based on the table instead. This will allow you to sort
by InvoiceNunber, among other things.

wrote:
Are you saying there are invoices with several line items, and that there may
be several such invoices per month for a client? Â*If so, are you further

[quoted text clipped - 54 lines]

- Show quoted text -


Normally only 1 invoice per client per month - that invoice could have
one or more item lines (each one representing a unique enquiry with
its own characteristics). I have a table of those enquiries - what I
am struggling with is how do I convert the enquiries into invoice item
lines and invoice headers without going through each client and
creating an invoice header and then selecting the item lines to add to
that invoice or worse still re-keying the item lines (enquiries).
Unless you can suddenly see what I am driving at (which cannot be easy
I know), I will adopt the approach I outlined in my previous post
above.

Gordon


--
Message posted via
http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.