difficuilt query

[k0r54]

Hi

I am new to this forum and fairly new to access, but i have been doing mysql for some time.

Ok, i need to create a query that can do this: -

I have two tables (tablea and tableb)

tablea has among other fields lineAdd, reported date
tableb has among other fields lineAdd, relevence date, adverage.

Ok what i need it the query to ask for information to be entered.

What i need is this : -
i want to show result where the adverage is less than 90 (popup to ask for this adverage number) and where the relevence and reported date are between 3 months (two popup boxes, 1 for the period number and another for the period type(day, week, month)) from the 01/06/05 (a popup box to ask for the from date)

So that is in total 4 popup boxes (adverage, period, period type and from date).

The lineAdd is the same on both of them so there is a relation between them on the query.

What makes this a bit more complecate is that i then need a 5th popup box which asks for the frequency. Now what i mean is, if i enter the number 2 in the frequency popup box it will only show me the result from above where the adverage is below 90 in 2 (the figure entered in frequency) or more of the 3 months between them dates.

I hope this is fairly clear, and mainly that some1 can help me.

Thanks
k0r54

 

[k0r54]

Is it a little unclear what i am after??

 

[k0r54]

Ok i have done all the first bit : - sql code =

SELECT Complete_call_list.[FS-ID], Complete_call_list.[FS-ID], Monthly_cleansed_figures.[Average Availability], Complete_call_list.Reported
FROM Complete_call_list INNER JOIN Monthly_cleansed_figures ON Complete_call_list.[FS-ID] = Monthly_cleansed_figures.[Line Address]
WHERE (((Monthly_cleansed_figures.[Average Availability])<[Average Availability:]) AND ((Complete_call_list.Reported) Between ([Starting Date:]) And (DateAdd([Period Type (m=month, d=day, y=year):],+[Period of time:],[Starting Date (Same as before):]))));

tablea = complete_call_list
tableb = monthly_cleansed_figures
FS-ID = lineadd for table a

The only thing left is : -

What makes this a bit more complecate is that i then need a 5th popup box which asks for the frequency. Now what i mean is, if i enter the number 2 in the frequency popup box it will only show me the result from above where the adverage is below 90 in 2 (the figure entered in frequency) or more of the 3 months between them dates.

Thanks
k0r54