Pivot query

[peter.thericgroup]

people, i have a frustrating issue, (if they all aren't?)...where i'm
opening a recordset, and the first time it gives me the error:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database
engine cannot find the input table or query 'Stock'. Make sure it
exists and that its name is spelled correctly.

But, then i try to open the recordset a secondtime, and it works fine!
cannot work what is hapenning here.

i will tell you that the tables i'm using in the query are linked
tables in an access database. but still, if it works the second time,
not sure how it works at all really, but it opens flawlessly the second
time round, the data is correct, etc.??? very strange, any help would
be great.

here is the query:

TRANSFORM Sum(qMedicalReport.DLineTotalInc) AS MaxOfDLineTotalInc
SELECT qMedicalReport.Staff, qMedicalReport.Customer,
Sum(qMedicalReport.DLineTotalInc) AS SumOfDLineTotalInc
FROM qMedicalReport
WHERE (((qMedicalReport.dept_id)=0 Or (qMedicalReport.dept_id)=1 Or
(qMedicalReport.dept_id)=2 Or (qMedicalReport.dept_id)=3) AND
((qMedicalReport.MaxOfdocket_date) Between #4/15/2006# And #4/20/2006
23:59:59#))
GROUP BY qMedicalReport.Staff, qMedicalReport.Customer
PIVOT qMedicalReport.MaxOfdept_name;

here is the qMedicalReport query that the pivot one above is based on:

SELECT Docket.docket_id, Max(Docket.docket_date) AS MaxOfdocket_date,
Max(Docket.total_ex) AS MaxOftotal_ex,
Sum(docketline.sell_ex*docketline.quantity) AS DLineTotal,
Stock.dept_id, Max(staff.barcode & ": " & staff.given_names & " " &
staff.surname) AS Staff, Max(Departments.dept_name) AS MaxOfdept_name,
Max(customer.barcode & ": " & customer.given_names & " " &
customer.surname) AS Customer,
Sum(docketline.sell_inc*docketline.quantity) AS DLineTotalInc,
Max(Docket.total_inc) AS MaxOftotal_inc
FROM (Stock LEFT JOIN Departments ON Stock.dept_id =
Departments.dept_id) RIGHT JOIN (Staff RIGHT JOIN ((Customer RIGHT JOIN
Docket ON Customer.customer_id = Docket.customer_id) INNER JOIN
DocketLine ON Docket.docket_id = DocketLine.docket_id) ON
Staff.staff_id = Docket.staff_id) ON Stock.stock_id =
DocketLine.stock_id
GROUP BY Docket.docket_id, Stock.dept_id
HAVING (((Stock.dept_id)=0 Or (Stock.dept_id)=1 Or (Stock.dept_id)=2 Or
(Stock.dept_id)=3))
ORDER BY Sum(docketline.sell_ex*docketline.quantity), Max(staff.barcode
& ": " & staff.given_names & " " & staff.surname);

 

[Duane Hookom]

You could try optimize your base query with this SQL:
SELECT Docket.docket_id, Max(Docket.docket_date) AS MaxOfdocket_date,
Max(Docket.total_ex) AS MaxOftotal_ex,
Sum(docketline.sell_ex*docketline.quantity) AS DLineTotal,
Stock.dept_id, Max(staff.barcode & ": " & staff.given_names & " " &
staff.surname) AS Staff, Max(Departments.dept_name) AS MaxOfdept_name,
Max(customer.barcode & ": " & customer.given_names & " " &
customer.surname) AS Customer,
Sum(docketline.sell_inc*docketline.quantity) AS DLineTotalInc,
Max(Docket.total_inc) AS MaxOftotal_inc
FROM (Stock LEFT JOIN Departments ON Stock.dept_id =
Departments.dept_id) RIGHT JOIN (Staff RIGHT JOIN ((Customer RIGHT JOIN
Docket ON Customer.customer_id = Docket.customer_id) INNER JOIN
DocketLine ON Docket.docket_id = DocketLine.docket_id) ON
Staff.staff_id = Docket.staff_id) ON Stock.stock_id =
DocketLine.stock_id
WHERE Stock.dept_id IN (0,1,2,3)
GROUP BY Docket.docket_id, Stock.dept_id
ORDER BY Sum(docketline.sell_ex*docketline.quantity), Max(staff.barcode
& ": " & staff.given_names & " " & staff.surname);
--
Duane Hookom
MS Access MVP

people, i have a frustrating issue, (if they all aren't?)...where i'm
opening a recordset, and the first time it gives me the error:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database
engine cannot find the input table or query 'Stock'. Make sure it
exists and that its name is spelled correctly.

But, then i try to open the recordset a secondtime, and it works fine!
cannot work what is hapenning here.

i will tell you that the tables i'm using in the query are linked
tables in an access database. but still, if it works the second time,
not sure how it works at all really, but it opens flawlessly the second
time round, the data is correct, etc.??? very strange, any help would
be great.

here is the query:

TRANSFORM Sum(qMedicalReport.DLineTotalInc) AS MaxOfDLineTotalInc
SELECT qMedicalReport.Staff, qMedicalReport.Customer,
Sum(qMedicalReport.DLineTotalInc) AS SumOfDLineTotalInc
FROM qMedicalReport
WHERE (((qMedicalReport.dept_id)=0 Or (qMedicalReport.dept_id)=1 Or
(qMedicalReport.dept_id)=2 Or (qMedicalReport.dept_id)=3) AND
((qMedicalReport.MaxOfdocket_date) Between #4/15/2006# And #4/20/2006
23:59:59#))
GROUP BY qMedicalReport.Staff, qMedicalReport.Customer
PIVOT qMedicalReport.MaxOfdept_name;

here is the qMedicalReport query that the pivot one above is based on:

SELECT Docket.docket_id, Max(Docket.docket_date) AS MaxOfdocket_date,
Max(Docket.total_ex) AS MaxOftotal_ex,
Sum(docketline.sell_ex*docketline.quantity) AS DLineTotal,
Stock.dept_id, Max(staff.barcode & ": " & staff.given_names & " " &
staff.surname) AS Staff, Max(Departments.dept_name) AS MaxOfdept_name,
Max(customer.barcode & ": " & customer.given_names & " " &
customer.surname) AS Customer,
Sum(docketline.sell_inc*docketline.quantity) AS DLineTotalInc,
Max(Docket.total_inc) AS MaxOftotal_inc
FROM (Stock LEFT JOIN Departments ON Stock.dept_id =
Departments.dept_id) RIGHT JOIN (Staff RIGHT JOIN ((Customer RIGHT JOIN
Docket ON Customer.customer_id = Docket.customer_id) INNER JOIN
DocketLine ON Docket.docket_id = DocketLine.docket_id) ON
Staff.staff_id = Docket.staff_id) ON Stock.stock_id =
DocketLine.stock_id
GROUP BY Docket.docket_id, Stock.dept_id
HAVING (((Stock.dept_id)=0 Or (Stock.dept_id)=1 Or (Stock.dept_id)=2 Or
(Stock.dept_id)=3))
ORDER BY Sum(docketline.sell_ex*docketline.quantity), Max(staff.barcode
& ": " & staff.given_names & " " & staff.surname);

 

[Gary Walter]

people, i have a frustrating issue, (if they all aren't?)...where i'm
opening a recordset, and the first time it gives me the error:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database
engine cannot find the input table or query 'Stock'. Make sure it
exists and that its name is spelled correctly.

But, then i try to open the recordset a secondtime, and it works fine!
cannot work what is hapenning here.

i will tell you that the tables i'm using in the query are linked
tables in an access database. but still, if it works the second time,
not sure how it works at all really, but it opens flawlessly the second
time round, the data is correct, etc.??? very strange, any help would
be great.

here is the query:

TRANSFORM Sum(qMedicalReport.DLineTotalInc) AS MaxOfDLineTotalInc
SELECT qMedicalReport.Staff, qMedicalReport.Customer,
Sum(qMedicalReport.DLineTotalInc) AS SumOfDLineTotalInc
FROM qMedicalReport
WHERE (((qMedicalReport.dept_id)=0 Or (qMedicalReport.dept_id)=1 Or
(qMedicalReport.dept_id)=2 Or (qMedicalReport.dept_id)=3) AND
((qMedicalReport.MaxOfdocket_date) Between #4/15/2006# And #4/20/2006
23:59:59#))
GROUP BY qMedicalReport.Staff, qMedicalReport.Customer
PIVOT qMedicalReport.MaxOfdept_name;

here is the qMedicalReport query that the pivot one above is based on:

SELECT Docket.docket_id, Max(Docket.docket_date) AS MaxOfdocket_date,
Max(Docket.total_ex) AS MaxOftotal_ex,
Sum(docketline.sell_ex*docketline.quantity) AS DLineTotal,
Stock.dept_id, Max(staff.barcode & ": " & staff.given_names & " " &
staff.surname) AS Staff, Max(Departments.dept_name) AS MaxOfdept_name,
Max(customer.barcode & ": " & customer.given_names & " " &
customer.surname) AS Customer,
Sum(docketline.sell_inc*docketline.quantity) AS DLineTotalInc,
Max(Docket.total_inc) AS MaxOftotal_inc
FROM (Stock LEFT JOIN Departments ON Stock.dept_id =
Departments.dept_id) RIGHT JOIN (Staff RIGHT JOIN ((Customer RIGHT JOIN
Docket ON Customer.customer_id = Docket.customer_id) INNER JOIN
DocketLine ON Docket.docket_id = DocketLine.docket_id) ON
Staff.staff_id = Docket.staff_id) ON Stock.stock_id =
DocketLine.stock_id
GROUP BY Docket.docket_id, Stock.dept_id
HAVING (((Stock.dept_id)=0 Or (Stock.dept_id)=1 Or (Stock.dept_id)=2 Or
(Stock.dept_id)=3))
ORDER BY Sum(docketline.sell_ex*docketline.quantity), Max(staff.barcode
& ": " & staff.given_names & " " & staff.surname);

Hi Peter,

PMFBI

I think I might create a "qMedicalReportForXTAB"
where you *skip the HAVING and ORDER BY clauses*,
plus...
would it really make a difference in data if you used
FIRST instead of MAX on your string concatenations?

Then feed that to your xtab.

I understand what Duane is saying about moving HAVING
to WHERE, but I know I have had data where sums were
not what I wanted in that case...maybe your data is different.

Are you constructing both of these queries on the fly in code,
i.e., I have had unsaved queries that cost more time in compiling
than in executing...the gist being that the first time gave it a "start"
which it could not complete, but left enough behind that second
time could complete.

I don't know if that is your problem, but I might try the above
suggestions.

If they don't work, there is always the method of last resort:

Empty a table, then append with data from "qMedicalReportForXTAB."
Then run xtab on the table. Sometimes you just have to do that.

good luck,

gary