Invoice Summary Report

[Megan Flaherty via AccessMonster.com]

Hi.
Need some help -
I'm creating an invoice database. I have a form that creates an invoice. It
works fine and it based mainly on the Northwind database.
There are a few differences.

1) I have separate prices for different prices. I'm doing a lookup and I
get the price and then store that price in the invoice details table.
Still working fine.

2) In addition to products that are in the products table, I am allowing
for special items in a separate table. It's almost a duplicate to the
regular items.

My problem today is that when I am trying to do the invoice summary I am
only getting details if I have rows in both tables. Most invoices only have
the first table.

I don't know the best approach.
Here is the current query.

SELECT qryInvoice.InvoiceID, qryInvoice.InvoiceDate, qryInvoice.CustomerID,
qryInvoice.Customer, qryInvoice.INVOICES.Freight, qryInvoice.Tax,
qryInvoice.Palletcharge, qryInvoice.[Resale No], qryInvoice.[Fuel Surcharge]
, qryInvoice.FOB, SHIPPER.Shipvia, [Order Subtotals].totProd, [Special
Order Subtotals].totSpec
FROM [Special Order Subtotals], [Order Subtotals], qryInvoice INNER JOIN
SHIPPER ON qryInvoice.ShipperID = SHIPPER.ShipperID
WHERE ((([qryInvoice]![ShipperID])=[SHIPPER]![ShipperID]) AND (([Order
Subtotals]![InvoiceID])=[qryInvoice]![InvoiceID]) AND (([Special Order
Subtotals]![InvoiceID])=[qryInvoice]![InvoiceID]))
ORDER BY qryInvoice.InvoiceID, qryInvoice.InvoiceDate;

I am not storing totals. Is that a problem? the qryInvoice is the invoice
of customer and invoice tables that is the data source behind the invoice
form.
The regular order totals are in the Order Subtotals and the other are in
the special order totals.

I don't know if this is enough information? Can anyone help?

thanks!!!

 

[PC Datasheet]

Since your special items table is "almost duplicate" to the regular items,
add additional fields to your products table so you can combine regular
items and special items in one table. Add a YesNo field named SpecialItem to
the table so you can set this field to True for special items.

Add a checkbox to your Details subform and bind it to the SpeciialItem field
in the table. Set its default value in the form to False. Use a combobox to
select products for the Details subform. Create a query for the rowsource of
the combobox. Include the fields ProductID, ProductName and SpecialItem. Set
the criteria for SpecialItem to:
Forms!NameOfInvoiceForm!NameOfSubformControl!SpecialItem
Set the following properties for the combobox:
Bound Column 1
Column Count 2
Column Width 0;1.5
Put the following code in the subform's Current event:
Me!NameOfCombobox.Requery
Put the following code in the Checkbox's AfterUpdate event:
Me!NameOfCombobox.Requery

Now when you enter items in the Details form, tick the checkbox for special
items. The combobox will then only list special items. For regular items, do
nothing in the checkbox and the combobox will only list regular items.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



When you

Hi.
Need some help -
I'm creating an invoice database. I have a form that creates an invoice. It
works fine and it based mainly on the Northwind database.
There are a few differences.

1) I have separate prices for different prices. I'm doing a lookup and I
get the price and then store that price in the invoice details table.
Still working fine.

2) In addition to products that are in the products table, I am allowing
for special items in a separate table. It's almost a duplicate to the
regular items.

My problem today is that when I am trying to do the invoice summary I am
only getting details if I have rows in both tables. Most invoices only have
the first table.

I don't know the best approach.
Here is the current query.

SELECT qryInvoice.InvoiceID, qryInvoice.InvoiceDate, qryInvoice.CustomerID,
qryInvoice.Customer, qryInvoice.INVOICES.Freight, qryInvoice.Tax,
qryInvoice.Palletcharge, qryInvoice.[Resale No], qryInvoice.[Fuel Surcharge]
, qryInvoice.FOB, SHIPPER.Shipvia, [Order Subtotals].totProd, [Special
Order Subtotals].totSpec
FROM [Special Order Subtotals], [Order Subtotals], qryInvoice INNER JOIN
SHIPPER ON qryInvoice.ShipperID = SHIPPER.ShipperID
WHERE ((([qryInvoice]![ShipperID])=[SHIPPER]![ShipperID]) AND (([Order
Subtotals]![InvoiceID])=[qryInvoice]![InvoiceID]) AND (([Special Order
Subtotals]![InvoiceID])=[qryInvoice]![InvoiceID]))
ORDER BY qryInvoice.InvoiceID, qryInvoice.InvoiceDate;

I am not storing totals. Is that a problem? the qryInvoice is the invoice
of customer and invoice tables that is the data source behind the invoice
form.
The regular order totals are in the Order Subtotals and the other are in
the special order totals.

I don't know if this is enough information? Can anyone help?

thanks!!!