Design of billing table/form

[Ron]

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

 

[TC]

Ron, if you plan to have a single record for each invoice, you are
definitely on the wrong track. You are absolutely correct in the problems
that you think might occur. Your gut feeling has served you well, at this
point!

Start by reading:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[Allen Browne]

One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

 

[ron]

Allen,

Thanks so much for this road map. I was guessing that a
separate table for bill details was the right way to go
but wasn't sure how to go about creating it. I created the
tables as you laid out here. I have only one question:
when I created a relationship between the InvoiceID in
tblInvoice (Autonumber primary key) and InvoiceID in
tblInvoiceDetail (Foreign key), I tried to check "enforce
referential integrity" and got an error that the fields
need to be of the same type. I presume this is because on
field is AutoNumber and one is Number. Is referential
integrity unnecessary in this scenario?

thanks again,
rOn

-----Original Message-----
One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

.

 

[Allen Browne]

Referential Interity is very important in this scenario.
In order to create it, the type and size must match.
To match an Autonumber, you need a field of type Number, size Long Integer.

In tblInvoiceDetail, make sure that the foreign key InvoiceID is a field of
type Number, size Long Integer. (Size is in the lower pane.) While you are
there, remove the default value of zero (senseless), and set the Required
property to Yes (prevents orphaned records).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks so much for this road map. I was guessing that a
separate table for bill details was the right way to go
but wasn't sure how to go about creating it. I created the
tables as you laid out here. I have only one question:
when I created a relationship between the InvoiceID in
tblInvoice (Autonumber primary key) and InvoiceID in
tblInvoiceDetail (Foreign key), I tried to check "enforce
referential integrity" and got an error that the fields
need to be of the same type. I presume this is because on
field is AutoNumber and one is Number. Is referential
integrity unnecessary in this scenario?

thanks again,
rOn

-----Original Message-----
One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

.

 

[ron]

Allen,

Your advice has been enormously helpful. I'm coming along
but I have a couple of questions.

1. I have my Invoice form with its InvoiceDetail subform
set up and working, but I don't want the foreign key
[InvoiceID] to be visible in my InvoiceDetail subform. If
I delete it I am no longer able to enter multiple records
in the subform. I get the following error:

You cannot add or change a record because a related record
is required in table Invoice.

I tried hiding the field but it didn't hide. (My subform
is in datasheet view.) It's not essential to hide this
field but my users are very computer shy and I want to
remove as much clutter as possible.

2. I can't figure out how to create the calculation for
the invoice total based on the line items of the invoice
detail

Thanks again. Please let me know if it would be more
appropriate for me to start new threads with these
continued questions about my design.

Thanks,
Ron


-----Original Message-----

One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

.

 

[Adrian Jansen]

In datasheet view, just drag its column width to zero.

In fact, as long as the InvoiceID field exists in the subform recordsource
( the query), it should still be possible to delete the associated textbox
in the subform itself, and have the subform work correctly.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Allen,

Your advice has been enormously helpful. I'm coming along
but I have a couple of questions.

1. I have my Invoice form with its InvoiceDetail subform
set up and working, but I don't want the foreign key
[InvoiceID] to be visible in my InvoiceDetail subform. If
I delete it I am no longer able to enter multiple records
in the subform. I get the following error:

You cannot add or change a record because a related record
is required in table Invoice.

I tried hiding the field but it didn't hide. (My subform
is in datasheet view.) It's not essential to hide this
field but my users are very computer shy and I want to
remove as much clutter as possible.

2. I can't figure out how to create the calculation for
the invoice total based on the line items of the invoice
detail

Thanks again. Please let me know if it would be more
appropriate for me to start new threads with these
continued questions about my design.

Thanks,
Ron


-----Original Message-----

One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

.

 

[Allen Browne]

A1: Foreign key field in subform.
As Adrian says, you don't need to have the InvoiceID visible in the subform,
so long as it in in the RecordSource of the subform.

A2: Total of invoice.
Do you have the Amount field in the query that is the RecordSource of the
form? If so:
1. From the Database Window, open the subform in design view.

2. Set the Default View property of the subform to Continuous Form.

3. If you do not see a Form Header and Form Footer section on your subform,
choose Form Header/Footer from the View menu.

4. In the subform place a text box with these properites:
ControlSource =Sum([Amount])
Format Currency

If you really want to retain continuous view, this control in the subform
will not be visible. However, you can add another text box on the main form
that reads the total from the invisible box and displays it on the main
form. For information on how to refer to a control on the subform, see:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Your advice has been enormously helpful. I'm coming along
but I have a couple of questions.

1. I have my Invoice form with its InvoiceDetail subform
set up and working, but I don't want the foreign key
[InvoiceID] to be visible in my InvoiceDetail subform. If
I delete it I am no longer able to enter multiple records
in the subform. I get the following error:

You cannot add or change a record because a related record
is required in table Invoice.

I tried hiding the field but it didn't hide. (My subform
is in datasheet view.) It's not essential to hide this
field but my users are very computer shy and I want to
remove as much clutter as possible.

2. I can't figure out how to create the calculation for
the invoice total based on the line items of the invoice
detail

Thanks again. Please let me know if it would be more
appropriate for me to start new threads with these
continued questions about my design.

Thanks,
Ron


-----Original Message-----

One invoice can contain many (number unkown) lines.
Create another table to hold the invoice line items.

tblInvoice:
InvoiceID AutoNumber primary key
InvoiceDate Date/Time date of invoice
ClientID Number (Long) foreign key to table of clients.

tblInvoiceDetail:
InvoiceDetailID AutoNumber primary key
InvoiceID Number (Long) foreign key to tblInvoice.InvoiceID
ServiceTypeID Number (Long) foreign key to a lookup table.
Quantity Number (Double)
UnitPrice Currency amount each.

The interface will be a main form bound to tblInvoice, with a subform for
the line items. Create a query to use as the RecordSource of the subform. In
the query, type a calculated field into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives you the extended price for each row.
Sum this Amount field to get the invoice total.

To see an example of how this works:
1. Open the Northwind sample database.
2. Choose Relationships from the Tools menu.
3. Look a the relationships between Orders and Order Details.
4. Now open the Orders form, and see how the line items work.
5. The query Order Details Extended demonstrates the calculated field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to design a (relatively) simple billing module
for our database. Its core is a table in which each record
represents one invoice. However, each invoice may have
multiple line items, depending on the type of service
performed. For example:

invoice# 112
1. Repair 10 hrs @ $20.00 = $200.00
2. Remodel 40 hrs @ $30.00 = $1200.00
3. Constru 10 hrs @ $15.00 = $150.00
Total $1550.00

The way I have it set up currently is to have a set of
fields in my table for each line item (e.g. [ServiceType1],
[Hours1], [Rate1], [ServiceType2], [Hours2], [Rates2],
etc.), but I'm starting to suspect my design is flawed.

My method seems convoluted and I'm afraid I'm going to
create problems for myself when I want to create reports
that are based on values that are pulled from different
fields. (For example, I'll want to create a report that
shows ALL work of type "Repair Work" whether it's in field
[ServiceType1], [ServiceType2], or [ServiceType3].

Guidance and advice is appreciated. Thanks.
Ron

.