alias query

[anil]

Hi all
i have written query using alias table to get results of parameter for
last 12 months which is as follows:

SELECT t.Town, t.AMonth, t.Ayear, t.ParameterName, t.SAMPLE_NO,
t.RESULT,
DateDiff("m",DateSerial(t.AYear,t.Amonth,1),DateSerial([Table1].[Ayear],[Table1].[Amonth],1))
AS DiffMonths
FROM table1 AS t
WHERE
(((DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),DateSerial([Table1].[AYear],[Table1].[AMonth],1)))
Between 0 And 11))
ORDER BY t.RESULT;

in this query AMonth and AYear are month and year of sample date.
So the problem is when I run the query it always ask for Table1.Ayear
and Table1.AMonth, although I have connected table1 year and month to
form which works fine as indiviual.Can I fix it so that I can directly
select Table1.Ayear and table1.Amonth from the form when it is open.OR
finally directly select this query in form.
thanks
anil

 

[John Spencer]

If the form is open and displays the information, you can reference the
control(s) on the form. Assuming a form named "MyForm" and controls named
AYear and AMonth then
[Forms]![MyForm]![AYear]
would reference the first control.

SELECT t.Town, t.AMonth, t.Ayear, t.ParameterName, t.SAMPLE_NO,
t.RESULT,
DateDiff("m",DateSerial(t.AYear,t.Amonth,1),DateSerial([Forms]![MyForm]![AYear],[Forms]![MyForm]![AMonth],1))
AS DiffMonths
FROM table1 AS t
WHERE
(((DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),DateSerial([Forms]![MyForm]![AYear],[Forms]![MyForm]![AMonth],1)))
Between 0 And 11))
ORDER BY t.RESULT;

 

[anil]

HI John
That thing is not working fine.
I am writing the table1 query also so u can tell my mistake better:
SELECT tblTowns.Town, LabData.ParameterName, LabData.SAMPLE_NO,
Month([SAMPLE DATE]) AS AMonth, Year([SAMPLE DATE]) AS Ayear,
LabData.RESULT, LabData.[SAMPLE DATE], LabData.Site_Code
FROM tblTowns INNER JOIN (tblSites INNER JOIN LabData ON tblSites.[SITE
CODE] = LabData.Site_Code) ON tblTowns.[Town Code] = tblSites.[Town
Code]
WHERE (((Month([SAMPLE DATE]))=Month([Forms]![Form1]![SelectMonth]))
AND ((Year([SAMPLE DATE]))=Year([Forms]![Form1]![SelectYear])))
ORDER BY LabData.[SAMPLE DATE];

i have done change in ur query help as [Forms]![Form1]![SelectMonth] in
the alias query t.
I ahve tried putting AMonth instead of SelectMonth But it is giving
error as'Numeric expression may contain too many elements,too complex
to be evaluated'
please correct my mistake or we need to do something else
thanks
anil

 

[John Spencer]

SELECT tblTowns.Town
, LabData.ParameterName
, LabData.SAMPLE_NO
, Month([SAMPLE DATE]) AS AMonth
, Year([SAMPLE DATE]) AS Ayear
, LabData.RESULT
, LabData.[SAMPLE DATE]
, LabData.Site_Code
FROM tblTowns INNER JOIN
(tblSites INNER JOIN
LabData ON tblSites.[SITE CODE] = LabData.Site_Code)
ON tblTowns.[Town Code] = tblSites.[Town Code]
WHERE (((Month([SAMPLE DATE]))=Month([Forms]![Form1]![SelectMonth]))
AND ((Year([SAMPLE DATE]))=Year([Forms]![Form1]![SelectYear])))
ORDER BY LabData.[SAMPLE DATE];

Do you mean that you are using the above query as the source for the query
you originally posted? If so, what are you trying to do? And where are
you trying to do this? I really don't understand what the DateDiff function
is supposed to do for you.

Can you describe in words what you are trying to acheive? Perhaps something
like, "I want to get the one year's worth of data based on the sample date
compared to a year and month I input on a form." If that is the case, all
you need to do is change the query above to use the following where clause.
Which given a year and month on the form will calculate a date range of one
year.

WHERE [Sample Date] Between
DateSerial(Forms]![Form1]![SelectYear],([Forms]![Form1]![SelectMonth]-11,1)
and
DateSerial(Forms]![Form1]![SelectYear],([Forms]![Form1]![SelectMonth]+1,0)

If you input Year 2006 and Month 2, the above will return all records with a
sample date between March 1, 2005 and Feb 28, 2006.

 

[anil]

Thanks john
Basically there was article in which he solved using alias query to get
last 12 months result .So i was using its hint to get the last12 months
result,but it seems better,
thanks
anil