Is It Possible To Query A Particular Field If It Exists

[dominicdunmow]

Hi there,

I have a query that queries a particular table. The fileds in the table are date related and so change depending on the records that are to be queried.

Is it possible through an IIF statement or other means only to query a field if it exists in the table.

The problem I've had is that I did include every month in the query, but then when the records in the table don't contain a particular date field, it falls over.

Can anyone help please?

 

[lespaul]

It would be more helpful if you described your table structure. It sounds non-normalized and a non-normalized table requires more work to extract the correct information from.

Leslie

 

[dominicdunmow]

I have gone through a normalisation process but I think it may be just the nature of subject matter.

Examples of the fields in the table are 'value of contract', 'start date', and then a spread of the value over each claendar quarter within the contract term.

Some contracts may have a 'revenue profile' that begins Q1 2007 and runs through to Q1 2009.

I need to represent these in a report. If I use the fields of Q1 2007, Q2 2007, Q3 2007 then if I have another contract that ranges from 2005 - 2006 it won't be picked up.

 

[lespaul]

So you are saying that when you get a new contract you add a new field to the table structure to the last quarter of that contract? Your added Field names are Q1 2007, Q2 2007, etc.?

Leslie

 

[dominicdunmow]

Its a little more complicated, the table fields are Q1, Q2 thru to Q20.

The exact date is determined by in a query as these are dependent on the contract start date.

So if the start date is 2005 Q1 then 'Q1' in the table will in truth represent 2005 Q1.

Whereas for another record if the start date is 2006 Q2, 'Q1' in the table for this record will truly represent 2006 Q2 and on...

 

[PHV]

Take a look here:

http://www.abcdataworks.com/fundamentals.htm

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

 

[lespaul]

ok, first off, by having fields named Q1, Q2, ..Q20, you don't have a normalized structure. Why don't you "show" me the structure and what results you want from your query. That's a LOT more helpful than getting one piece of information at a time!

So please, tell me the tablename, describe the data and show what the expected results are.

Leslie

 

[dominicdunmow]

Ok

The fields of the query that I am querying (as there are joins with other queries) are:-

ID Service Value 2007Q1 2007Q2 2007Q3 2007Q4 2008Q1

These fields are based on existing data in the table the field names come through varius crosstabs and normalising queries.

If a record has a profile spread from 2006Q1 to 2007Q1 then these will appear as fields in the query too.

It is at this point that I have the problem as when building the query the fields are based on existing and future records that I may not see at this point.

 

[lespaul]

except if you want to be able to adjust the months then you need to get the data directly from the table, not from other queries. Again, it's not hard to create a query that normalizes your data and then use that as the basis for your final query.

Leslie