Help designing query 1

[NeilT123]

I am sorry but I have being trying to work this out and am now so confused that I am not sure which way to turn so any help would be appreciated.

I am trying to build a query that I will then use in a form and a report and is based on the application of a fertiliser to a crop.

When a fertiliser is applied to a crop the effect that it can have on a crop can last several years. So I currently have an incidence of an application of fertiliser and fields called
FieldName (this is a field in the agricultural sense not an Access field)
Year of Application
EffectYr0
EffectYr1
EffectYr2 (these are created based on a calculation)

The table I want to end up with would be something like this

Field Name Effect2003 Effect2004 Effect2005 Effect2006 and so on.
Example 1 XXX XXX XXX XXX
Example 2 XXX XXX XXX XXX

The Effect could be an accumulation of several different years applications eg Effect2005 could be an accumulation of applications in 2003, 2004 and 2005.

Some years there may be no applications at all.

I had this working once but it was based on a query based on a query based on a query and the reporting was so slow that I thought there must be a more efficient way of setting this up.
I am quite new to Access, OK with simple queries but not sure how to build this one. Please does anyone have any suggestions how I should set this up? I will gladly supply more info if required.
Thank you in advance.

 

[PHV]

Have you tried a crosstab query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeilT123]

Hi PH, I started to but have never done a cross tab query. I couldn't see how to convert EffectYr0,1 and 2 into column headings based on specific years so stopped and decided to ask for help before I got in too deep.

I am quite happy to explore CTQ if you think that is the best option. Pls Adv.

 

[dhookom]

You would first need to normalize your table that includes the EffectYr fields:

SELECT FieldName, EffectYr0 as Effect, [Year Of Application] As Yr
FROM tblNoNameProvided
UNION ALL
SELECT FieldName, EffectYr1, [Year Of Application]+1
FROM tblNoNameProvided
UNION ALL
SELECT FieldName, EffectYr2, [Year Of Application]+2
FROM tblNoNameProvided;

You can then create a crosstab based on the union query that has FieldName as the Row Heading, Yr as the Column Heading, and Sum(Effect) as the Value.

You may want to change the column heading to a relative year like
ColHead: "Yr" & Yr-Year(Date())
Then set the Column Headings property to:
"Yr0","Yr1","Y2"
This will display current effect as Yr0 and Yr2 will be 2 years out.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NeilT123]

Thanks Duane that worked perfectly.

I have added a lot of extra data into the query and now it consists of
Table then
Select Query then
Union Query then
Crosstab Query + 2 additional tables then
Report.

As I am new to this are there any rules/tips about where best to enter data. Eg is it better to add data before the union or after the union or does it not matter. Should a union be based on a table or is it OK to base it on another query, etc.
Any pointers appreciated.

Thanks once again

 

[dhookom]

I'm not sure what "enter data" has to do with creating queries and reports. Entering data should be done in forms, action queries, or code.

If you are more concerned about the optimizing of your queries, I would make sure that fields used in joins and criteria are indexed. Also, consider using "UNION ALL" rather than just "UNION".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]