Is this Query possible

P

P.McFarlane

I have been trying to create a query that shows some totals and stats (along
with some other stuff) and have reached a dead end. I don't even know if is
possible.

I have three tables; the first (called QAK1Data) holds project information.
Each record is data belonging to a unique JobNo. The second (called
BillingData) contains records of the day to day activity of a project. A
particular project may have none or many records. The third (called
InvoiceData) contains records of invoices for a project. A particular
project may have none or several records.

The following SQL statement almost works. Returned records are correct
EXCEPT when there is more than one InvoiceData record for a project. If
there are two InvoiceData records then the Costs and Charges records are
doubled and the Invoices record is halved. Any help in resolving this would
be appreciated.

SELECT QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client, QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt, Sum(BillingData.ThisCost) AS Costs,
Sum(BillingData.TotalCharge) AS Charges,
Sum([InvoiceData]![TotalCharge])/Count([BillingData]![JobNo]) AS Invoices,
(IIf([Invoices]=0,0,([invoices]/[Charges])*100)) AS Recovered,
IIf(Val([OrderValue])=0,'',[Charges]-[OrderValue]) AS QuoteDiff
FROM (InvoiceData RIGHT JOIN QAK1Data ON InvoiceData.JobNo = QAK1Data.JobNo)
LEFT JOIN BillingData ON QAK1Data.JobNo = BillingData.JobNo
GROUP BY QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client, QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt
HAVING (((QAK1Data.DateRecvd) Between DateValue('1/07/2008') And
DateValue('31/07/2008')))
ORDER BY QAK1Data.JobNo;
 
A

Allen Browne

You will need to stack one query on top of another.


Create a query using the BillingData table.
Depress the Total button on the toolbar.
Group By the unique project number (JobNo?)
Sum the amount.
Save the query.

Now create another query like the one you have, but without replace the
BillingData table with the query you just saved. Since the query returns at
most one record per JobNo (since you grouped on that), the problem of
duplicated rows has gone.
 
P

P.McFarlane

Thanks for the reply. I understand what you are saying, however the query is
to be used in a vb6 programme and not in the Access environment. I also
understand that the query(s) can be in the database and run from within VB.
This method does not allow me to implement a system that filters the
original query and has multiple undo.

What I would like to achieve, if possible, is a single query.

Thanks


Allen Browne said:
You will need to stack one query on top of another.


Create a query using the BillingData table.
Depress the Total button on the toolbar.
Group By the unique project number (JobNo?)
Sum the amount.
Save the query.

Now create another query like the one you have, but without replace the
BillingData table with the query you just saved. Since the query returns at
most one record per JobNo (since you grouped on that), the problem of
duplicated rows has gone.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

P.McFarlane said:
I have been trying to create a query that shows some totals and stats
(along
with some other stuff) and have reached a dead end. I don't even know if
is
possible.

I have three tables; the first (called QAK1Data) holds project
information.
Each record is data belonging to a unique JobNo. The second (called
BillingData) contains records of the day to day activity of a project. A
particular project may have none or many records. The third (called
InvoiceData) contains records of invoices for a project. A particular
project may have none or several records.

The following SQL statement almost works. Returned records are correct
EXCEPT when there is more than one InvoiceData record for a project. If
there are two InvoiceData records then the Costs and Charges records are
doubled and the Invoices record is halved. Any help in resolving this
would
be appreciated.

SELECT QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client, QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt, Sum(BillingData.ThisCost) AS Costs,
Sum(BillingData.TotalCharge) AS Charges,
Sum([InvoiceData]![TotalCharge])/Count([BillingData]![JobNo]) AS Invoices,
(IIf([Invoices]=0,0,([invoices]/[Charges])*100)) AS Recovered,
IIf(Val([OrderValue])=0,'',[Charges]-[OrderValue]) AS QuoteDiff
FROM (InvoiceData RIGHT JOIN QAK1Data ON InvoiceData.JobNo =
QAK1Data.JobNo)
LEFT JOIN BillingData ON QAK1Data.JobNo = BillingData.JobNo
GROUP BY QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client, QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt
HAVING (((QAK1Data.DateRecvd) Between DateValue('1/07/2008') And
DateValue('31/07/2008')))
ORDER BY QAK1Data.JobNo;
Click to expand...
Click to expand...
 
A

Allen Browne

To do it all in one, you will need to learn about subqueries:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

P.McFarlane said:
Thanks for the reply. I understand what you are saying, however the query
is
to be used in a vb6 programme and not in the Access environment. I also
understand that the query(s) can be in the database and run from within
VB.
This method does not allow me to implement a system that filters the
original query and has multiple undo.

What I would like to achieve, if possible, is a single query.

Thanks


Allen Browne said:
You will need to stack one query on top of another.


Create a query using the BillingData table.
Depress the Total button on the toolbar.
Group By the unique project number (JobNo?)
Sum the amount.
Save the query.

Now create another query like the one you have, but without replace the
BillingData table with the query you just saved. Since the query returns at
most one record per JobNo (since you grouped on that), the problem of
duplicated rows has gone.

P.McFarlane said:
I have been trying to create a query that shows some totals and stats
(along
with some other stuff) and have reached a dead end. I don't even know
if
is
possible.

I have three tables; the first (called QAK1Data) holds project
information.
Each record is data belonging to a unique JobNo. The second (called
BillingData) contains records of the day to day activity of a project.
A
particular project may have none or many records. The third (called
InvoiceData) contains records of invoices for a project. A particular
project may have none or several records.

The following SQL statement almost works. Returned records are correct
EXCEPT when there is more than one InvoiceData record for a project. If
there are two InvoiceData records then the Costs and Charges records
are
doubled and the Invoices record is halved. Any help in resolving this
would
be appreciated.

SELECT QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client,
QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt, Sum(BillingData.ThisCost) AS Costs,
Sum(BillingData.TotalCharge) AS Charges,
Sum([InvoiceData]![TotalCharge])/Count([BillingData]![JobNo]) AS Invoices,
(IIf([Invoices]=0,0,([invoices]/[Charges])*100)) AS Recovered,
IIf(Val([OrderValue])=0,'',[Charges]-[OrderValue]) AS QuoteDiff
FROM (InvoiceData RIGHT JOIN QAK1Data ON InvoiceData.JobNo =
QAK1Data.JobNo)
LEFT JOIN BillingData ON QAK1Data.JobNo = BillingData.JobNo
GROUP BY QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client,
QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt
HAVING (((QAK1Data.DateRecvd) Between DateValue('1/07/2008') And
DateValue('31/07/2008')))
ORDER BY QAK1Data.JobNo;
Click to expand...
Click to expand...
Click to expand...
 
P

P.McFarlane

Allen

Thanks for the link. I still have a lot to learn about subqueries (well
queries really) but have solved my problem. Works like charm

Thank a lot

Cheers


Allen Browne said:
To do it all in one, you will need to learn about subqueries:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

P.McFarlane said:
Thanks for the reply. I understand what you are saying, however the query
is
to be used in a vb6 programme and not in the Access environment. I also
understand that the query(s) can be in the database and run from within
VB.
This method does not allow me to implement a system that filters the
original query and has multiple undo.

What I would like to achieve, if possible, is a single query.

Thanks


Allen Browne said:
You will need to stack one query on top of another.


Create a query using the BillingData table.
Depress the Total button on the toolbar.
Group By the unique project number (JobNo?)
Sum the amount.
Save the query.

Now create another query like the one you have, but without replace the
BillingData table with the query you just saved. Since the query
Click to expand...
returns
at
most one record per JobNo (since you grouped on that), the problem of
duplicated rows has gone.

I have been trying to create a query that shows some totals and stats
(along
with some other stuff) and have reached a dead end. I don't even know
if
is
possible.

I have three tables; the first (called QAK1Data) holds project
information.
Each record is data belonging to a unique JobNo. The second (called
BillingData) contains records of the day to day activity of a project.
A
particular project may have none or many records. The third (called
InvoiceData) contains records of invoices for a project. A particular
project may have none or several records.

The following SQL statement almost works. Returned records are correct
EXCEPT when there is more than one InvoiceData record for a project. If
there are two InvoiceData records then the Costs and Charges records
are
doubled and the Invoices record is halved. Any help in resolving this
would
be appreciated.

SELECT QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client,
QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt, Sum(BillingData.ThisCost) AS Costs,
Sum(BillingData.TotalCharge) AS Charges,
Sum([InvoiceData]![TotalCharge])/Count([BillingData]![JobNo]) AS Invoices,
(IIf([Invoices]=0,0,([invoices]/[Charges])*100)) AS Recovered,
IIf(Val([OrderValue])=0,'',[Charges]-[OrderValue]) AS QuoteDiff
FROM (InvoiceData RIGHT JOIN QAK1Data ON InvoiceData.JobNo =
QAK1Data.JobNo)
LEFT JOIN BillingData ON QAK1Data.JobNo = BillingData.JobNo
GROUP BY QAK1Data.JobNo, QAK1Data.QuoteOnly, QAK1Data.OrderNo,
QAK1Data.OrderValue, QAK1Data.Desc, QAK1Data.Client,
QAK1Data.DateRecvd,
QAK1Data.ProjectType, QAK1Data.ClientType, QAK1Data.FeeRange,
QAK1Data.ProjectManager, QAK1Data.InstructionSource, QAK1Data.Closed,
QAK1Data.ClosedDate, QAK1Data.BadDebt
HAVING (((QAK1Data.DateRecvd) Between DateValue('1/07/2008') And
DateValue('31/07/2008')))
ORDER BY QAK1Data.JobNo;
Click to expand...
Click to expand...
Click to expand...
 
Top