By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Using templates to create automated invoices for customers

P: 27
Hi,
I have a database which will process orders; i have an option on my form that should allow users to automatically generate an invoice, how would I go about creating a template (in the form of a report) that can automatically include the customers details (i.e. their address)?! im relatively new to Access so any help would be hugely appreciated. Thanks a lot!
Feb 9 '07 #1
Share this Question
Share on Google+
25 Replies


P: 9
By the sound of what you are asking, you just need to create a Query for your report to be based on. The Query should include fields from your Orders table and the required customer data fields from your Customers table.

In order to advise further, we would need to know what tables you have, structure and relationships.
Feb 11 '07 #2

P: 27
By the sound of what you are asking, you just need to create a Query for your report to be based on. The Query should include fields from your Orders table and the required customer data fields from your Customers table.

In order to advise further, we would need to know what tables you have, structure and relationships.

I have a customers table with the following:
Customer ID
title
forename
surname
(address details)
email address

this is linked (one to many) to tblOrders; with:
orderID
date
OrderLineID
CustomerID
Total <--- SHOULD I STORE A CALCULATED FIELD

tblOrderLine

OrderLineID
Quantity
ProductID
Sub Total

the products has ProductID and ProductType, Price and various other details...
Feb 11 '07 #3

NeoPa
Expert Mod 15k+
P: 31,487
I've put together for you an initial stab at the table MetaData. There are still many gaps to be filled in to help us to help you. BTW Your (address details) is fine as it's not used anywhere to link or find records. (Most of) the other fields need the full information though before we can correctly suggest answers.
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblCustomers
  2. Customer ID; ????; PK (Is this Autonumber or Text?)
  3. title; Text
  4. forename; Text
  5. surname; Text
  6. (address details); All Text
  7. email address; Text
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrders
  2. orderID; ????; PK
  3. date; Date/Time
  4. OrderLineID  (This surely can't be in here - it makes no sense???)
  5. CustomerID; ????; FK (Name different from PK of tblCustomers???)
  6. Total; Numeric No - Don't store calculated field
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrderLine
  2. OrderID; ????; PK & FK (I've added this as it's surely required.
  3. OrderLineID; ????; PK
  4. Quantity; Numeric
  5. ProductID; ????; FK
  6. Sub Total; Numeric (Another calculated field)
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblProduct (guess)
  2. ProductID; ????; PK
  3. ProductType; ????
  4. Price; Numeric
When you have this properly sorted then you can either link to it in your other questions or (even better) copy it and paste it into them all.
Also :
tblCustomers
tblOrders
tblOrderLine (no s)
If you are not consistent with your naming you will have problems later. It is so easy to link to tblCustomer rather than tblCustomers by mistake.
Feb 11 '07 #4

P: 27
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblCustomers
  2. Customer ID; Autonumber; PK 
  3. title; Text
  4. forename; Text
  5. surname; Text
  6. (address details); All Text
  7. email address; Text
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrders
  2. orderID; Autonumber; PK
  3. date; Date/Time
  4. OrderLineID  (WHAT ELSE?)
  5. CustomerID; foreign key!; FK 
  6.  
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrderLine
  2. OrderID; FOREIGN KEY; PK & FK (I've added this as it's surely required.
  3. OrderLineID; PRIMARY KEY; PK
  4. Quantity; Numeric
  5. ProductID; FOREIGN KEY; FK
  6. Sub Total; Numeric (Another calculated field)
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblProduct (guess)
  2. ProductID; PRIMARY KEY; PK
  3. ProductType; Text
  4. Price; Numeric
Hope this helps makes things a little clearer.
Feb 11 '07 #5

NeoPa
Expert Mod 15k+
P: 31,487
Now we're cooking :)
I've just realised that I should change my template to make it clearer what the three fields are :
Field; Type; IndexInfo
IndexInfo codes we use are PK=Primary Key & FK=Foreign Key. Sometimes multiple fields make up a key but we can normally tell that by context.
Never mind, I'll fix the template another time.
Some questions for you :
  1. What are the Types (Text; Numeric or Date/Time) of :
    1. OrderLineID?
    2. ProductID?
  2. How can you have an OrderLineID in the tblOrders table? There can surely be multiple order lines per order so which could be stored in the header (tblOrder)? and what use could it be?
  3. Do you still want tblOrderLine.[Sub Total] stored (the recommended answer is no of course)?
Feb 11 '07 #6

P: 27
Now we're cooking :)
I've just realised that I should change my template to make it clearer what the three fields are :
Field; Type; IndexInfo
IndexInfo codes we use are PK=Primary Key & FK=Foreign Key. Sometimes multiple fields make up a key but we can normally tell that by context.
Never mind, I'll fix the template another time.
Some questions for you :
  1. What are the Types (Text; Numeric or Date/Time) of :
    1. OrderLineID?
    2. ProductID?
  2. How can you have an OrderLineID in the tblOrders table? There can surely be multiple order lines per order so which could be stored in the header (tblOrder)? and what use could it be?
  3. Do you still want tblOrderLine.[Sub Total] stored (the recommended answer is no of course)?
OrderLineID is a Primary key, autonumber and productID is the same

and sorry i made a mistake about OrderLineID in the tblOrders it's OrderID in OrderLine table :-)

and no i do not want to store any calculated fields, its becoming way to complicated if i do. i guess it's easier the other way every1 recommends., im new to this so i guess if its recommended it's probably the best way. :-)
Feb 11 '07 #7

NeoPa
Expert Mod 15k+
P: 31,487
Good answers to all questions.
Now I'm going to post the full set in a separate post. When you need to reuse it, click on Reply and copy the resulting text (this will include all the display formatting) except the [quote=NeoPa] and the [ /Quote] bits. This can be posted in any question where this info is required and will only take a couple of seconds.

One final question - Is the OrderLineID a PK field on its own or is it included with OrderID to make a PK together?
If you see anything wrong with what I've posted just let me know and I'll fix it.
Feb 11 '07 #8

NeoPa
Expert Mod 15k+
P: 31,487
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblCustomers
  2. Customer ID; Autonumber; PK
  3. title; Text
  4. forename; Text
  5. surname; Text
  6. (address details); All Text
  7. email address; Text
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrders
  2. orderID; Autonumber; PK
  3. date; Date/Time
  4. CustomerID; Numeric; FK
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblOrderLine
  2. OrderLineID; Autonumber; PK
  3. OrderID; Numeric; FK
  4. Quantity; Numeric
  5. ProductID; Numeric; FK
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblProducts
  2. ProductID; Autonumber; PK
  3. ProductType; Text
  4. Price; Numeric
Feb 11 '07 #9

P: 27
[quote=NeoPa]Good answers to all questions.
Now I'm going to post the full set in a separate post. When you need to reuse it, click on Reply and copy the resulting text (this will include all the display formatting) except the
and the [ /Quote] bits. This can be posted in any question where this info is required and will only take a couple of seconds.

One final question - Is the OrderLineID a PK field on its own or is it included with OrderID to make a PK together?
If you see anything wrong with what I've posted just let me know and I'll fix it.

No, OrderLineID is a primary key field on it's own, i don't have any composite keys if thats what your referring to :-)
Feb 11 '07 #10

NeoPa
Expert Mod 15k+
P: 31,487
It certainly was ;)
Does the post I did (#9) reflect your database correctly then?
Feb 11 '07 #11

P: 27
It certainly was ;)
Does the post I did (#9) reflect your database correctly then?
Yep.
I have done a query
Expand|Select|Wrap|Line Numbers
  1. SELECT [Price]*[Quantity] AS [Sub Total]
  2. FROM tblProducts INNER JOIN
  3.      (tblOrders INNER JOIN tblOrderLine
  4.      ON tblOrders.OrderID = tblOrderLine.OrderID)
  5.      ON tblProducts.ProductID = tblOrderLine.ProductID;
Which does calculate the sub total, however, i would like to display this in a form and a report, also, how would the sub totals all be totalled together?!
If need you need more information about it, just ask :-)
Feb 11 '07 #12

P: 27
Oh, and also on my form frmOrder it has a sub form tblOrderLine, which has a combo box which displays make and model of the tblProducts and is linked to the ProductID, and the quantity is a text box, so would the sub total field be a text box or ...?
and also how would it look up Price from tblProducts
Feb 11 '07 #13

NeoPa
Expert Mod 15k+
P: 31,487
Yep.
I have done a query
Expand|Select|Wrap|Line Numbers
  1. SELECT [Price]*[Quantity] AS [Sub Total]
  2. FROM tblProducts INNER JOIN
  3.      (tblOrders INNER JOIN tblOrderLine
  4.      ON tblOrders.OrderID = tblOrderLine.OrderID)
  5.      ON tblProducts.ProductID = tblOrderLine.ProductID;
Which does calculate the sub total, however, i would like to display this in a form and a report, also, how would the sub totals all be totalled together?!
If need you need more information about it, just ask :-)
Your code would seem to indicate that the Product table is called tblProducts rather than the tblProduct that I had. I will update the post accordingly.
When using multiple tables in a query it is good practice to qualify the fieldnames used. With only one table it's better not to though.
Expand|Select|Wrap|Line Numbers
  1. SELECT P.Price*OL.Quantity AS [Sub Total]
  2. FROM (tblOrders AS O INNER JOIN tblOrderLine AS OL
  3.      ON O.OrderID=OL.OrderID)
  4.      INNER JOIN tblProducts AS P
  5.      ON OL.ProductID=P.ProductID;
To display this query on a form or report you need to set it's RecordSource property to the SQL or a QueryDef (saved query) with that SQL. This would be called a bound form or report (although I think all reports are bound in that respect).
To answer how the subtotals would be grouped together I need to understand how your report is grouping its data.
Feb 11 '07 #14

NeoPa
Expert Mod 15k+
P: 31,487
Oh, and also on my form frmOrder it has a sub form tblOrderLine, which has a combo box which displays make and model of the tblProducts and is linked to the ProductID, and the quantity is a text box, so would the sub total field be a text box or ...?
and also how would it look up Price from tblProducts
So you have some more fields to tell me about? In tblProducts? Or in tblOrderLine? Or both?
What is the current RecordSources of the form & subform?
Please post the two sets of SQL so I can tell what's going on.
Feb 11 '07 #15

P: 27
So you have some more fields to tell me about? In tblProducts? Or in tblOrderLine? Or both?
What is the current RecordSources of the form & subform?
Please post the two sets of SQL so I can tell what's going on.

Right, ok, i changed the SQL to what you provided and that didnt change a thing so it's going OK
on the OrderLine form (which is the subform) how do i get the record source?
I thought if the record source was tblOrderLine (which im assuming it is) and i changed that to SQL it wouldn't work?! Ok, maybe i'm wrong.
The main form is frmOrder which sub form tblOrderLine and sub form again tblOrder
if theres anything more you need to know just let me know.
i cannot tell you the current record source as i dont know how to view that info :-(
im assuming though that the main forms record source is tblCustomers.
and tblOrderLine form is tblOrderLine
Feb 11 '07 #16

NeoPa
Expert Mod 15k+
P: 31,487
Right, ok, i changed the SQL to what you provided and that didnt change a thing so it's going OK
on the OrderLine form (which is the subform) how do i get the record source?
I thought if the record source was tblOrderLine (which im assuming it is) and i changed that to SQL it wouldn't work?! Ok, maybe i'm wrong.
The main form is frmOrder which sub form tblOrderLine and sub form again tblOrder
if theres anything more you need to know just let me know.
i cannot tell you the current record source as i dont know how to view that info :-(
im assuming though that the main forms record source is tblCustomers.
and tblOrderLine form is tblOrderLine
To get the Record Source of the main form :
  1. Open it in Design View.
  2. Make sure Properties are showing.
  3. The Record Source is the Property at the top.
  4. Select it and use Ctrl-C to Copy the contents.
To get the Record Source of the subform :
  1. Now click on the subform (anywhere in the subform area).
  2. Click on the grey square at the intersection of the two rulers.
  3. The Record Source is the Property at the top.
  4. Select it and use Ctrl-C to Copy the contents.
Feb 11 '07 #17

P: 27
To get the Record Source of the main form :
  1. Open it in Design View.
  2. Make sure Properties are showing.
  3. The Record Source is the Property at the top.
  4. Select it and use Ctrl-C to Copy the contents.
To get the Record Source of the subform :
  1. Now click on the subform (anywhere in the subform area).
  2. Click on the grey square at the intersection of the two rulers.
  3. The Record Source is the Property at the top.
  4. Select it and use Ctrl-C to Copy the contents.

Recrd Source for sub form tblOrderLine: tblOrderLine

Recrd Source for sub form tblOrderLine: tblOrders

Recrd Source for main form frmOrder: tblCustomers
Feb 11 '07 #18

NeoPa
Expert Mod 15k+
P: 31,487
Recrd Source for sub form tblOrderLine: tblOrderLine

Recrd Source for sub form tblOrderLine: tblOrders

Recrd Source for main form frmOrder: tblCustomers
Can you confirm :
You have a main form called frmOrder with two subforms - both called tblOrderLine, but one of them has a record source of tblOrders?

I would expect there to be a main form (frmCustomers) - tblCustomers.
This would have a subform (frmOrders) - tblOrders.
This subform would itself have a subform (frmOrderLine) - tblOrderLine.

Or you would simply have a main form (frmOrders) - tblOrders.
This form would have a subform (frmOrderLine) - tblOrderLine.

If you do have a setup as you describe, can you explain why and what you expect to do with it?
Feb 11 '07 #19

P: 27
Can you confirm :
You have a main form called frmOrder with two subforms - both called tblOrderLine, but one of them has a record source of tblOrders?

I would expect there to be a main form (frmCustomers) - tblCustomers.
This would have a subform (frmOrders) - tblOrders.
This subform would itself have a subform (frmOrderLine) - tblOrderLine.

Or you would simply have a main form (frmOrders) - tblOrders.
This form would have a subform (frmOrderLine) - tblOrderLine.

If you do have a setup as you describe, can you explain why and what you expect to do with it?
I have:
a main form (frmCustomers) - tblCustomers.
This would have a subform (frmOrders) - tblOrders.
This main form has another subform (frmOrderLine) - tblOrderLine.
What i wish to do is be able to calculate sub totals and totals as users select the product (a combo box linked to ProductID in tblOrderLine) and Quantity also from the same table.
Feb 11 '07 #20

P: 9
Eek, I already have this DB created from a project I did long time back. Do you have an email address if you would like to see it?
Feb 12 '07 #21

NeoPa
Expert Mod 15k+
P: 31,487
I have:
a main form (frmCustomers) - tblCustomers.
This would have a subform (frmOrders) - tblOrders.
This main form has another subform (frmOrderLine) - tblOrderLine.
What i wish to do is be able to calculate sub totals and totals as users select the product (a combo box linked to ProductID in tblOrderLine) and Quantity also from the same table.
You know you need to answer all questions before I can continue with this. Every time I try to look at this I find there is either missing info or incorrect info that leaves me confused or stuck. Your habit of moving from one question to another before the previous one is resolved doesn't help me to understand any better.
When I last looked at where we were I found a question in post #15 that had no response. I can't promise there are no more as I stopped checking at that point.
Feb 12 '07 #22

P: 27
Eek, I already have this DB created from a project I did long time back. Do you have an email address if you would like to see it?
Yeah, thanks, you can mail it here: u2sneppahtihs at hotmail dot com
Feb 12 '07 #23

P: 27
You know you need to answer all questions before I can continue with this. Every time I try to look at this I find there is either missing info or incorrect info that leaves me confused or stuck. Your habit of moving from one question to another before the previous one is resolved doesn't help me to understand any better.
When I last looked at where we were I found a question in post #15 that had no response. I can't promise there are no more as I stopped checking at that point.

I am trying my best to give you as much info as possible which is clear and makes sense.
I thought i clearly explained about the forms and sub forms i have, icluding there record source.
Feb 12 '07 #24

NeoPa
Expert Mod 15k+
P: 31,487
I guess you're trying, but I'm afraid I still can't continue without a response to post #15. I suppose you don't appreciate how hard it is to do this remotely without precise and accurate information (otherwise how can my answer match your situation). Never mind, lets just forget about it.
Feb 12 '07 #25

P: 9
Eek, spologies for the delay. DB just emailed to you. Hoping that you can dissect and understand it for what you want. ;-)
Mar 1 '07 #26

Post your reply

Sign in to post your reply or Sign up for a free account.