Hello,
I am working in Access 2003. I have a Vendors table and a PurchaseOrders table in a one to many relationship. The PO table holds, among other fields, the amount of each order, amount closed (paid off) so far (some orders are paid off in parts) and a requisition number that identifies the fiscal year in which the order was placed (in format 2012.00# where '00#' is the unique requisition number for fiscal year 2012, for example). What I am trying to do is get a sum per vendor of all fiscal year 2012 orders and the total of these that is closed so far, and the same for previous fiscal years' orders. I then want to use the resulting fields SpentFy12, ClosedFy12, Spent_previous, Closed_previous in a VendorProfile form which will give the user a quick snapshot of all the open encumbrances per vendor.
I am trying to accomplish the Spent and Closed sums in two separate queries:
SELECT
V.VendorName,
sum(P.POAmountActual) AS SpentFY12,
sum(ClosedAmount) AS ClosedFY12
FROM
(Vendors AS V) LEFT JOIN
(SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*' ) AS P
ON V.VendorName=P.VendorName
GROUP BY V.VendorName;
(the query for the previous fiscal years is identical except for 'not like '2012*''.
The problem is that this query works just great when I format it like that and run it. Then, as soon as I close it and go to create a form based on this query, I get a
'the recordsource you specified does not exist' error. I go back to check what's wrong with the query and I can't run it! I get the following error:
"the jet engine cannot find the source table or query
'[SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*'; ]...'
What keeps happening is that Access keeps putting a semicolon at the end of the select statement that is in the right position of the LEFT JOIN and after it does that, it can't run the query. Once I open it in SQL view and change the formatting to the way I had it, it runs perfectly again, but as soon as I save and close, I get the same problem all over again.
I tried repair the database, but still getting the same outcome. Can anyone please help with this tricky situation?
Thank you so much in advance!
-Margarita
I am working in Access 2003. I have a Vendors table and a PurchaseOrders table in a one to many relationship. The PO table holds, among other fields, the amount of each order, amount closed (paid off) so far (some orders are paid off in parts) and a requisition number that identifies the fiscal year in which the order was placed (in format 2012.00# where '00#' is the unique requisition number for fiscal year 2012, for example). What I am trying to do is get a sum per vendor of all fiscal year 2012 orders and the total of these that is closed so far, and the same for previous fiscal years' orders. I then want to use the resulting fields SpentFy12, ClosedFy12, Spent_previous, Closed_previous in a VendorProfile form which will give the user a quick snapshot of all the open encumbrances per vendor.
I am trying to accomplish the Spent and Closed sums in two separate queries:
SELECT
V.VendorName,
sum(P.POAmountActual) AS SpentFY12,
sum(ClosedAmount) AS ClosedFY12
FROM
(Vendors AS V) LEFT JOIN
(SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*' ) AS P
ON V.VendorName=P.VendorName
GROUP BY V.VendorName;
(the query for the previous fiscal years is identical except for 'not like '2012*''.
The problem is that this query works just great when I format it like that and run it. Then, as soon as I close it and go to create a form based on this query, I get a
'the recordsource you specified does not exist' error. I go back to check what's wrong with the query and I can't run it! I get the following error:
"the jet engine cannot find the source table or query
'[SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*'; ]...'
What keeps happening is that Access keeps putting a semicolon at the end of the select statement that is in the right position of the LEFT JOIN and after it does that, it can't run the query. Once I open it in SQL view and change the formatting to the way I had it, it runs perfectly again, but as soon as I save and close, I get the same problem all over again.
I tried repair the database, but still getting the same outcome. Can anyone please help with this tricky situation?
Thank you so much in advance!
-Margarita