Pivot query

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);
---------------------------------------------------
i hope this makes sense, and that you can shed some light on my issue,
thanks very much.

.

Relevant Pages

  • Re: Help with Microsoft help on linking Access to Outlook
    ... Tools - references - tick Microsoft Outlook object library. ... in the criteria cell in the graphical query builder. ... the same filtered recordset every time. ... normal form to allow the normal filtering process and viewing of the ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... normal form to allow the normal filtering process and viewing of the ...
    (microsoft.public.access.formscoding)
  • Re: Change Control Source
    ... recordset in code for the purposes of building the string. ... base your report on a query that pulls the appropriate customers, ... customer ID ...
    (microsoft.public.access.reports)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... and have the query point to the combo box to get its value. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... normal form to allow the normal filtering process and viewing of the ...
    (microsoft.public.access.formscoding)
Loading