Help with query for invoice report

[graeme34 via AccessMonster.com]

Hi
Could somebody please give me some advice, I am trying to create an Invoice
report. All was going well until I tried to summarise the Invoice totals in
the report footer.

The problem I have is there is a VAT Rate control I have in the child report,
Invoice details. I managed to accomplish getting the value of this control
into my master report footer. The VAT Rate is the primary key in the VAT
table where I need to get the VAT value from, I tried the DLookup function
and had no success....so I posted the question in the Modules/DAO/Topics.....
but unfortunately I had no replies.

So I decided to tinker about with the control source query from the master
file to try and bring the VAT value I require into the report that way, this
I managed only the query was duplicating the records one for each order line,
so I grouped them together and thought that was it...hey presto....
But after testing the report with different eventulaties I realised that if
two invoices, i.e. a part order is sent then another despatch the rest of the
order is sent, are required for the one order then two records are created
when the query is opened, for the second Invoice hence printing out two
invoices. Here is the SQL from the query builder:

SELECT tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber, tblSalesOrder.
CustomerOrderNumber, tblSalesOrder.DeliveryMode, tblSalesInvoice.
SalesInvoiceNumber, tblDespatch.DespatchNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine ON tblSalesOrder.SalesOrderNumber =
tblSalesOrderLine.SalesOrderNumber) ON tblVAT.VATRate = tblSalesOrderLine.
VATRate
GROUP BY tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber, tblSalesOrder.
CustomerOrderNumber, tblSalesOrder.DeliveryMode, tblSalesInvoice.
SalesInvoiceNumber, tblDespatch.DespatchNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue
HAVING (((tblDespatch.SalesOrderNumber)=[Forms]![frmChooseOrderNumber]!
[txtOrderNumberChoice]));

I have tried manually delete the fields that are causing the problem in the
Group By clause, such as the despatch and invoice attributes it wont let me
do this.

Is there any way to enforce the query to just select the record with highest
despatch number and Invoice number, I tried used the max function but this
just created an alias field.

Alternatively could somebody tell me how to use the Dlookup function for an
unbound control in the report footer. I have already posted the question
about 5 hours ago but had no joy
I need something along the lines of :
=DLookUp("VATValue","tblVAT","VATRate = " & subrepInvLine.Report![VATRate])

I have experimented with other criteria such as = & Reports!repInvoice!
txtVATRate

Where txtVATRate is also a control in the footer set to = subrepInvLine.
Report!VATRate

Thank you !!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Getting the highest dispatch numbers:

SELECT DISTINCT A.AccountName, A.[Account Address 1], A.[Account Address
2], A.[Account Address 3], A.[Account Town/ City], A.[Post Code],
D.SalesOrderNumber, SO.CustomerOrderNumber, SO.DeliveryMode,
SI.SalesInvoiceNumber, D.DespatchNumber, SI.InvoiceDate,
SI.TotalNett, SOL.VATRate, V.VATValue

FROM tblVAT AS V INNER JOIN (((tblAccount AS A INNER JOIN tblSalesOrder
AS SO ON A.AccountIndex = SO.AccountIndex) INNER JOIN (tblDespatch AS D
INNER JOIN tblSalesInvoice AS SI ON D.DespatchNumber = SI.[Despatch
Number]) ON SO.SalesOrderNumber = D.SalesOrderNumber) INNER JOIN
tblSalesOrderLine AS SOL ON SO.SalesOrderNumber = SOL.SalesOrderNumber)
ON V.VATRate = SOL.VATRate

WHERE
D.SalesOrderNumber=[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND SI.[Despatch Number] = (SELECT MAX([Despatch Number]) FROM
tblSalesInvoice WHERE SalesInvoiceNumber = SI.SalesInvoiceNumber)

To get the Max invoice number, I couldn't figure out the relationship
between your tables that would allow me to create the criteria.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRBSdOYechKqOuFEgEQIj/gCgwo+oGIwQkdDBPeRR2Ui+5bEaKDoAniEY
q8A2fMTQRJQ+go6w5nEk14Rd
=EzZH
-----END PGP SIGNATURE-----

Hi
Could somebody please give me some advice, I am trying to create an Invoice
report. All was going well until I tried to summarise the Invoice totals in
the report footer.

The problem I have is there is a VAT Rate control I have in the child report,
Invoice details. I managed to accomplish getting the value of this control
into my master report footer. The VAT Rate is the primary key in the VAT
table where I need to get the VAT value from, I tried the DLookup function
and had no success....so I posted the question in the Modules/DAO/Topics.....
but unfortunately I had no replies.

So I decided to tinker about with the control source query from the master
file to try and bring the VAT value I require into the report that way, this
I managed only the query was duplicating the records one for each order line,
so I grouped them together and thought that was it...hey presto....
But after testing the report with different eventulaties I realised that if
two invoices, i.e. a part order is sent then another despatch the rest of the
order is sent, are required for the one order then two records are created
when the query is opened, for the second Invoice hence printing out two
invoices. Here is the SQL from the query builder:

SELECT tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber, tblSalesOrder.
CustomerOrderNumber, tblSalesOrder.DeliveryMode, tblSalesInvoice.
SalesInvoiceNumber, tblDespatch.DespatchNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine ON tblSalesOrder.SalesOrderNumber =
tblSalesOrderLine.SalesOrderNumber) ON tblVAT.VATRate = tblSalesOrderLine.
VATRate
GROUP BY tblAccount.AccountName, tblAccount.[Account Address 1], tblAccount.
[Account Address 2], tblAccount.[Account Address 3], tblAccount.[Account Town
/ City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber, tblSalesOrder.
CustomerOrderNumber, tblSalesOrder.DeliveryMode, tblSalesInvoice.
SalesInvoiceNumber, tblDespatch.DespatchNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue
HAVING (((tblDespatch.SalesOrderNumber)=[Forms]![frmChooseOrderNumber]!
[txtOrderNumberChoice]));

I have tried manually delete the fields that are causing the problem in the
Group By clause, such as the despatch and invoice attributes it wont let me
do this.

Is there any way to enforce the query to just select the record with highest
despatch number and Invoice number, I tried used the max function but this
just created an alias field.

Alternatively could somebody tell me how to use the Dlookup function for an
unbound control in the report footer. I have already posted the question
about 5 hours ago but had no joy
I need something along the lines of :
=DLookUp("VATValue","tblVAT","VATRate = " & subrepInvLine.Report![VATRate])

I have experimented with other criteria such as = & Reports!repInvoice!
txtVATRate

Where txtVATRate is also a control in the footer set to = subrepInvLine.
Report!VATRate

Thank you !!

 

[graeme34 via AccessMonster.com]

Thank you very much, it worked ......
just out of curiousity do you know how I could get the dlookup function to
work ??
Full question is in Modules/DAO/VBA....if not still very grateful

Getting the highest dispatch numbers:

SELECT DISTINCT A.AccountName, A.[Account Address 1], A.[Account Address
2], A.[Account Address 3], A.[Account Town/ City], A.[Post Code],
D.SalesOrderNumber, SO.CustomerOrderNumber, SO.DeliveryMode,
SI.SalesInvoiceNumber, D.DespatchNumber, SI.InvoiceDate,
SI.TotalNett, SOL.VATRate, V.VATValue

FROM tblVAT AS V INNER JOIN (((tblAccount AS A INNER JOIN tblSalesOrder
AS SO ON A.AccountIndex = SO.AccountIndex) INNER JOIN (tblDespatch AS D
INNER JOIN tblSalesInvoice AS SI ON D.DespatchNumber = SI.[Despatch
Number]) ON SO.SalesOrderNumber = D.SalesOrderNumber) INNER JOIN
tblSalesOrderLine AS SOL ON SO.SalesOrderNumber = SOL.SalesOrderNumber)
ON V.VATRate = SOL.VATRate

WHERE
D.SalesOrderNumber=[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND SI.[Despatch Number] = (SELECT MAX([Despatch Number]) FROM
tblSalesInvoice WHERE SalesInvoiceNumber = SI.SalesInvoiceNumber)

To get the Max invoice number, I couldn't figure out the relationship
between your tables that would allow me to create the criteria.

Hi
Could somebody please give me some advice, I am trying to create an Invoice

[quoted text clipped - 61 lines]

Thank you !!