Too many fields in a query

[rutica]

There are 2 types of paths a project can follow: ELC and SDLC. In my database, the user must choose which path they are following for their project.

Once they choose a path, they must then fill in one of the two appropriate grid with dates.

The two grids have around 40 fields each.

I am trying to write a query that looks at the response entered for Path and then looks to see if the user entered dates in the wrong grid. (If they choose ELC, they shouldn't have values in the SDLC grid).

For example: iif(Path="ELC" and SDLCDate1<>Null or SDLCDate2<>Null or ...,SDLCDate40<>Null,"Red",Null)

But typing SDLCDate1...SDLCDate40 <> Null is time consuming, messy and Access probably won't allow such a big query.

Is there any workaround?

Thanks,

 

[ted.martin]

One way may be to add another field to your table called (say) CheckDate; make it a Yes/No with No or False as the default value.

Assuming your data is filled in via a form, for each of the 40 fields on the form, on the AfterUpdate and maybe the LostFocus event - add this line of code

If SDLCDate1<>Null then ' change figure for each field - ie SDLCDate2, SDLCDate3 etc.
Docmd.CheckDate.Value = True
else
Docmd.CheckDate.Value = False
end if


When you want to query the data, all you need to do is check with CheckDate is True or False.

If Both paths are in the same table as the 40 or so fields, then you may need two fields CheckDateELC and CheckDateSDLC. It would be better to separate these two Paths into two tables.

 

[neileg]

Sounds like you need to normalise your database. Instead of 40 fields in the table you should have 40 records in a related table.

 

[rutica]

Thanks to both of you for writing!

The data is dumped from a web-based application.

Ted: The web-based application is where the user enter the data, so I can't do AfterUpdate code or add fields to the table. But that was a good suggestion.

Neileg: Good point about normalizing. You're right. But since I get the raw data from an external source, that won't work.

Oh well.