Help with query!

  • Thread starter graeme34 via AccessMonster.com
  • Start date
G

graeme34 via AccessMonster.com

Hi I have a query is used for the control source of a report, Monthly
Statements

The query was running fine when it was simply showing all debits, now Ive
tried to adapt it by joining another table, transaction which stores details
of all customer payments, as well as supplier payments. Here is the original
query:

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit
FROM tblVAT INNER JOIN ((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex=tblSalesOrder.AccountIndex) INNER JOIN ((tblDespatch INNER JOIN
tblSalesOrderLine ON tblDespatch.SalesOrderNumber=tblSalesOrderLine.
SalesOrderNumber) INNER JOIN tblSalesInvoice ON tblDespatch.
DespatchNumber=tblSalesInvoice.[Despatch Number]) ON (tblSalesOrder.
SalesOrderNumber=tblDespatch.SalesOrderNumber) AND (tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber)) ON tblVAT.
VATRate=tblSalesOrderLine.VATRate
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

After adapting it to 'supposedly' show the details of any payments
it now looks like :

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit, tblTransaction.TypeOfPosting, IIf([TypeOfPosting]="Customer
Payment",[Amount],0) AS Credit
FROM tblTransaction INNER JOIN (tblVAT INNER JOIN ((tblAccount INNER JOIN
tblSalesOrder ON tblAccount.AccountIndex = tblSalesOrder.AccountIndex) INNER
JOIN ((tblDespatch INNER JOIN tblSalesOrderLine ON tblDespatch.
SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber) INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON (tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.
SalesOrderNumber) AND (tblSalesOrder.SalesOrderNumber = tblDespatch.
SalesOrderNumber)) ON tblVAT.VATRate = tblSalesOrderLine.VATRate) ON
tblTransaction.TransactionNumber = tblSalesInvoice.TransactionNumber
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

The query works but only showing the records that have had a payment. I know
this is because of the INNER JOIN, but could anybody tell me is there a way
to display the result of the first query as well as being able to display any
payment details made from the transaction table??

Thank you
 
K

Ken Snell \(MVP\)

In the QBE window for the query in design view, right-click on the join line
between the two tables of interest. Select Edit from the menu. Choose the
option that allows all records from the parent table, and just the matching
records from the child table. Click OK.

--

Ken Snell
<MS ACCESS MVP>



graeme34 via AccessMonster.com said:
Hi I have a query is used for the control source of a report, Monthly
Statements

The query was running fine when it was simply showing all debits, now Ive
tried to adapt it by joining another table, transaction which stores
details
of all customer payments, as well as supplier payments. Here is the
original
query:

SELECT DISTINCT tblAccount.AccountIndex,
tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue,
[TotalNett]*(([VatValue]/100)+1)
AS Debit
FROM tblVAT INNER JOIN ((tblAccount INNER JOIN tblSalesOrder ON
tblAccount.
AccountIndex=tblSalesOrder.AccountIndex) INNER JOIN ((tblDespatch INNER
JOIN
tblSalesOrderLine ON tblDespatch.SalesOrderNumber=tblSalesOrderLine.
SalesOrderNumber) INNER JOIN tblSalesInvoice ON tblDespatch.
DespatchNumber=tblSalesInvoice.[Despatch Number]) ON (tblSalesOrder.
SalesOrderNumber=tblDespatch.SalesOrderNumber) AND (tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber)) ON tblVAT.
VATRate=tblSalesOrderLine.VATRate
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

After adapting it to 'supposedly' show the details of any payments
it now looks like :

SELECT DISTINCT tblAccount.AccountIndex,
tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue,
[TotalNett]*(([VatValue]/100)+1)
AS Debit, tblTransaction.TypeOfPosting, IIf([TypeOfPosting]="Customer
Payment",[Amount],0) AS Credit
FROM tblTransaction INNER JOIN (tblVAT INNER JOIN ((tblAccount INNER JOIN
tblSalesOrder ON tblAccount.AccountIndex = tblSalesOrder.AccountIndex)
INNER
JOIN ((tblDespatch INNER JOIN tblSalesOrderLine ON tblDespatch.
SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber) INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON (tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.
SalesOrderNumber) AND (tblSalesOrder.SalesOrderNumber = tblDespatch.
SalesOrderNumber)) ON tblVAT.VATRate = tblSalesOrderLine.VATRate) ON
tblTransaction.TransactionNumber = tblSalesInvoice.TransactionNumber
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

The query works but only showing the records that have had a payment. I
know
this is because of the INNER JOIN, but could anybody tell me is there a
way
to display the result of the first query as well as being able to display
any
payment details made from the transaction table??

Thank you
Click to expand...
 
J

John Spencer

You might try changing the join to an outer join. TEST the following which uses
a right join. If this works it will return all records in the other tables.

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit, tblTransaction.TypeOfPosting, IIf([TypeOfPosting]="Customer
Payment",[Amount],0) AS Credit
FROM tblTransaction RIGHT JOIN (tblVAT INNER JOIN ((tblAccount INNER JOIN
tblSalesOrder ON tblAccount.AccountIndex = tblSalesOrder.AccountIndex) INNER
JOIN ((tblDespatch INNER JOIN tblSalesOrderLine ON tblDespatch.
SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber) INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON (tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.
SalesOrderNumber) AND (tblSalesOrder.SalesOrderNumber = tblDespatch.
SalesOrderNumber)) ON tblVAT.VATRate = tblSalesOrderLine.VATRate) ON
tblTransaction.TransactionNumber = tblSalesInvoice.TransactionNumber
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

graeme34 via AccessMonster.com said:
Hi I have a query is used for the control source of a report, Monthly
Statements

The query was running fine when it was simply showing all debits, now Ive
tried to adapt it by joining another table, transaction which stores details
of all customer payments, as well as supplier payments. Here is the original
query:

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit
FROM tblVAT INNER JOIN ((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex=tblSalesOrder.AccountIndex) INNER JOIN ((tblDespatch INNER JOIN
tblSalesOrderLine ON tblDespatch.SalesOrderNumber=tblSalesOrderLine.
SalesOrderNumber) INNER JOIN tblSalesInvoice ON tblDespatch.
DespatchNumber=tblSalesInvoice.[Despatch Number]) ON (tblSalesOrder.
SalesOrderNumber=tblDespatch.SalesOrderNumber) AND (tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber)) ON tblVAT.
VATRate=tblSalesOrderLine.VATRate
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

After adapting it to 'supposedly' show the details of any payments
it now looks like :

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit, tblTransaction.TypeOfPosting, IIf([TypeOfPosting]="Customer
Payment",[Amount],0) AS Credit
FROM tblTransaction INNER JOIN (tblVAT INNER JOIN ((tblAccount INNER JOIN
tblSalesOrder ON tblAccount.AccountIndex = tblSalesOrder.AccountIndex) INNER
JOIN ((tblDespatch INNER JOIN tblSalesOrderLine ON tblDespatch.
SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber) INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON (tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.
SalesOrderNumber) AND (tblSalesOrder.SalesOrderNumber = tblDespatch.
SalesOrderNumber)) ON tblVAT.VATRate = tblSalesOrderLine.VATRate) ON
tblTransaction.TransactionNumber = tblSalesInvoice.TransactionNumber
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

The query works but only showing the records that have had a payment. I know
this is because of the INNER JOIN, but could anybody tell me is there a way
to display the result of the first query as well as being able to display any
payment details made from the transaction table??

Thank you
Click to expand...
 
G

graeme34 via AccessMonster.com

Thank you Ken ! (and John)
In the QBE window for the query in design view, right-click on the join line
between the two tables of interest. Select Edit from the menu. Choose the
option that allows all records from the parent table, and just the matching
records from the child table. Click OK.
Hi I have a query is used for the control source of a report, Monthly
Statements
Click to expand...
[quoted text clipped - 55 lines]
Thank you
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with logic on a query.. 12
opening report is askiing for parameter value of subreport ? 1
Help with query for invoice report 2
Help with my despatch note report 1
Help with populating a report 8
Help with criteria on select query 13
Query not updatable..... 4
Help with query 6

Top