Invoice report

[mogens]

I have three tables from which I would like to create an invoice:

orders
Contains 1 line per order with header information

orders_products
Contains 1 line per product included in the order. Each line to be
printed on the invoice

orders_total
A table including invoice totals for the order with a least 1 line per
order. This could be a line for the product subtotal, a line for
freight, a line for tax and a line for the invoice grand total.

I do not quite understand how to make orders_products print out its
lines first, and then after this print the lines from orders_total.

Can it be done using subreports, or???

 

[Jeff Boyce]

Given what you described, yes, you can create a "main report" and
"sub-reports" and embed them within the main report.

 

[mogens]

Given what you described, yes, you can create a "main report" and
"sub-reports" and embed them within the main report.

What puzzles me here, is that the I for for instance order No 1 has to
print 2 lines from orders_products followed by 3 lines from
orders_total, then order No 2 etc. The tables orders_products and
orders_total are both linked to orders_id in table orders, so they are
at the same "level".

I cannot quite see where to fit this in into the main report. Would I
have to create a subreport for product lines based on orders_product
(report1), then a subreport of order total lines (report 2), and then
add these after each others in the detail section at order_id level in
the main report?

Hope this is too unclearly described

 

[mogens]

You might want to check out the Northwinds sample db in Access. In
2003, it is under Help, Sample databases. They do what you want to
do.

Thanks Bob. Tried to check that out, and you are right, it seems as this
is precisely what I'm looking for.

A related question: I'm doing this for a total of four companies, so I
have four identical databases. I can easily copy the sql for the queries
and reuse this to create identical queries for the other databases. But
how about the report code? Will I have to manually build-up from scratch
for each database, or is there a way to copy this code between databases?

 

[Jeff Boyce]

It is still too unclearly described. Could you post the SQL statements for
the queries underlying the reports?

 

[mogens]

It is still too unclearly described. Could you post the SQL statements for
the queries underlying the reports?

Sure. Hope it is not too messy ;-)

SELECT orders.orders_id, orders.suppl_onum, orders.suppl_note,
orders.date_purchased, orders.payment_method, orders.customers_name,
orders.customers_company, orders.customers_street_address,
orders.customers_suburb, orders.customers_city,
orders.customers_postcode, orders.customers_country,
orders.customers_telephone, orders.customers_email_address,
orders.delivery_name, orders.delivery_company,
orders.delivery_street_address, orders.delivery_suburb,
orders.delivery_city, orders.delivery_postcode, orders.delivery_country,
orders.eu_vat, orders_products.products_model,
orders_products.products_name, orders_products.products_price,
[final_price]*[currency_value] AS stykpris_excl,
orders_products.products_tax, orders_products.products_quantity,
Replace(Replace(orders_total!title,"<b>",""),"</b>","") AS titel,
Replace(Replace(orders_total!text,"<b>",""),"</b>","") AS tekst,
orders_total.value, orders_total.class, orders_total.sort_order,
currencies.symbol_left, currencies.symbol_right,
lang_texts.invoice_title, lang_texts.invoice_number,
lang_texts.invoice_date, lang_texts.sold_to, lang_texts.ship_to,
lang_texts.payment_method, lang_texts.payment_date, lang_texts.items,
lang_texts.item_no, lang_texts.tax, lang_texts.price_excl,
lang_texts.price_incl, lang_texts.total, lang_texts.tax_no_text,
lang_texts.tax_no,
[stykpris_excl]*orders_products!products_quantity*(1+orders_products!products_tax/100)
AS totalpris_inkl, orders_total.orders_id,
orders_products.orders_products_id
FROM (countries INNER JOIN (((orders INNER JOIN orders_products ON
orders.orders_id = orders_products.orders_id) INNER JOIN orders_total ON
orders.orders_id = orders_total.orders_id) INNER JOIN currencies ON
orders.currency = currencies.code) ON countries.countries_name =
orders.delivery_country) INNER JOIN lang_texts ON countries.lang_code =
lang_texts.language_id
WHERE (((orders.orders_id)>=10065))
ORDER BY orders_total.sort_order, orders_total.orders_id;

 

[Jeff Boyce]

So, you mentioned main reports and subreports ... which does the SQL you
posted apply to?

--
Regards

Jeff Boyce
<Office/Access MVP>

It is still too unclearly described. Could you post the SQL statements for
the queries underlying the reports?

Sure. Hope it is not too messy ;-)

SELECT orders.orders_id, orders.suppl_onum, orders.suppl_note,
orders.date_purchased, orders.payment_method, orders.customers_name,
orders.customers_company, orders.customers_street_address,
orders.customers_suburb, orders.customers_city,
orders.customers_postcode, orders.customers_country,
orders.customers_telephone, orders.customers_email_address,
orders.delivery_name, orders.delivery_company,
orders.delivery_street_address, orders.delivery_suburb,
orders.delivery_city, orders.delivery_postcode, orders.delivery_country,
orders.eu_vat, orders_products.products_model,
orders_products.products_name, orders_products.products_price,
[final_price]*[currency_value] AS stykpris_excl,
orders_products.products_tax, orders_products.products_quantity,
Replace(Replace(orders_total!title,"<b>",""),"</b>","") AS titel,
Replace(Replace(orders_total!text,"<b>",""),"</b>","") AS tekst,
orders_total.value, orders_total.class, orders_total.sort_order,
currencies.symbol_left, currencies.symbol_right,
lang_texts.invoice_title, lang_texts.invoice_number,
lang_texts.invoice_date, lang_texts.sold_to, lang_texts.ship_to,
lang_texts.payment_method, lang_texts.payment_date, lang_texts.items,
lang_texts.item_no, lang_texts.tax, lang_texts.price_excl,
lang_texts.price_incl, lang_texts.total, lang_texts.tax_no_text,
lang_texts.tax_no,
[stykpris_excl]*orders_products!products_quantity*(1+orders_products!product
s_tax/100)
AS totalpris_inkl, orders_total.orders_id,
orders_products.orders_products_id
FROM (countries INNER JOIN (((orders INNER JOIN orders_products ON
orders.orders_id = orders_products.orders_id) INNER JOIN orders_total ON
orders.orders_id = orders_total.orders_id) INNER JOIN currencies ON
orders.currency = currencies.code) ON countries.countries_name =
orders.delivery_country) INNER JOIN lang_texts ON countries.lang_code =
lang_texts.language_id
WHERE (((orders.orders_id)>=10065))
ORDER BY orders_total.sort_order, orders_total.orders_id;

 

[mogens]

So, you mentioned main reports and subreports ... which does the SQL you
posted apply to?

Jeff, I tried to make the report without subreports. Having worked a
little further, I have now solved the problem by inserting a subreport
in the order_id.footer containing the orders_total lines. Works beautiful!