Sorry about the caps, just a habit from work,
thanks!
Rick B said:
Open your query in design view, then click VIEW then SQL VIEW. Post your
queries for us.
All queries are simply SQL statements.
--
Rick B
well the subquery contains a function that makes it only display the
fields
which contain data for the past tree months from a table. In other words
this query chooses only the fields that contain data from the last three
months and uses the months number as a column heading. as i mentioned
below,
this subquery is not the problem. the problem is when i merge this query
with another query and create the main query. in this main query as the
months go by the column heading are not updated instead what this query
does
is try to find the column heading from the original three months it had
and
obviously one of them is now missing because the subquery has updated
itself
and changed one of the column headings.
ex: The current month is november , the subquery will have 8,9, and 10 as
column headings and since the main query obtains its records from this
subquery, it will also display the fields 8, 9, and 10. Now the problem
occurs when December comes by and now the subquery eliminates the column
heading 8 and replaces it with 11. (again this query is designed to only
show data for the past 3 months so now it will display 9, 10, and 11).
the main query will not recognize this and will attempt to open a field
named "8" which is no longer there because it has been replaced by an
"11".
there is no sql involved in this, (im not that knowledgable yet).
what i have to do now to fix this, is actually modify the main query
design
and replace the "8" field with an "11" field.
i hope this is clear enough
thank you
:
Depends on how you created the months.
If you used a dateadd function or similar, then it seems it would always
work.
I'd think you would have to post your SQL in order for anyone to answer
this
question.
Also, you say it does not work at the beginning of each month, what do
you
have to do to get it working?
--
Rick B
The problem that I am trying to solve is the following,
I have a query that is composed of two other querys. One of these
queries
shows data for the last 3 months, so the fields would look something
like
this if the current month is november for example:
[client id], [8], [9], [10], [total]
this query updates the month numbers every month, if the current month
was
december then it would show data for months 9, 10 & 11 and so on.
this subquery works perfectly fine, the problem that im having is with
the
main query. Every time it is the first of the month it doesnt
recognize
one
of its fields which is the month that has been updated by the subquery
and
i
get an error message.
i guess the question would be:
is there any way that i can get the main query to also update the
months
every month?
Juan,
941 LAST QTR TAX DEPOSITS_CROSSTAB
TRANSFORM Sum([941 Last Qtr Tax Deposits].Deposit) AS SumOfDeposit
SELECT [941 Last Qtr Tax Deposits].[Client ID]
,[941 Last Qtr Tax Deposits].Year
,Sum([941 Last Qtr Tax Deposits].Deposit) AS [Total Of
Deposit]
FROM [941 Last Qtr Tax Deposits]
GROUP BY [941 Last Qtr Tax Deposits].[Client ID]
,[941 Last Qtr Tax Deposits].Year
PIVOT [941 Last Qtr Tax Deposits].Month;
941 INFO QUERY
SELECT [CLIENT DATABASE LIST].[COMPANY NAME]
,[CLIENT DATABASE LIST].[PERSON TO CONTACT]
,[CLIENT DATABASE LIST].[PHONE #]
,[CLIENT DATABASE LIST].[2ND PHONE #]
,[CLIENT DATABASE LIST].EFTPS
,[941 Last Qtr Tax Deposits_Crosstab].Year
,[941 Last Qtr Tax Deposits_Crosstab].[8]
,[941 Last Qtr Tax Deposits_Crosstab].[9]
,[941 Last Qtr Tax Deposits_Crosstab].[10]
,[941 Last Qtr Tax Deposits_Crosstab].[Total Of Deposit]
,[CLIENT DATABASE LIST].[CLIENT ID]
,[CLIENT DATABASE LIST].[941 Preparer]
FROM [CLIENT DATABASE LIST]
INNER JOIN
[941 Last Qtr Tax Deposits_Crosstab]
ON [CLIENT DATABASE LIST].[CLIENT ID] =
[941 Last Qtr Tax Deposits_Crosstab].[Client ID]
WHERE [CLIENT DATABASE LIST].PAYROLL = Yes;
From your original post:
"Every time it is the first of the month it doesnt recognize one
of its fields which is the month that has been updated by the subquery
and i
get an error message."
I cannot see that the first query updates anything. It is a SELECT
query, not an UPDATE query. May I as for more details on "it doesnt
recognize one
of its fields which is the month that has been updated by the
subquery"?
Also, what is the exact error message that you are receiving?
Sincerely,
Chris O.
PS Although meant for microsoft.public.sqlserver.programming, the
following
link is still applicable for microsoft.public.access.queries:
http://www.aspfaq.com/etiquette.asp?id=5006, and is excellent when it
comes to detailing how to provide the information that will best
enable
others to answer your questions.
