Stumped (1 Viewer)

[Lyncroft]

I've got a form based on a table. Just part of this form is made up of 8 yes/no fields which can be split up into 2 groups as below and 8 date boxes. Each record looks like below:

License Number


Activity 1 Y/N Date Confirmed Y/N Date
Activity 2 Y/N Date Confirmed Y/N Date
Activity 3 Y/N Date Confirmed Y/N Date
Activity 4 Y/N Date Confirmed Y/N Date


What I want to do is to produce a query for example showing all license numbers where activity 2 is Y and confirmed is Y and corresponding dates.

I realise that with a better structured table this would be easier but I'm stuck with what I've got. Cant see a way around it.

 

[David R]

Why are you 'stuck' with what you've got? You may spend more time fixing problems due to repeating fields than you would to go ahead and fix the data now.

Example: I started with a database with 200 entries. It was horribly unnormalized, as Pat Hartman and some others can recall. I fixed some of the problems then but my boss didn't want to change the entire structure. 2900 entries later it took me a week and a half to finally fix the structure when she realized it was necessary. It would have been 10x easier to do when we had only 200 entries instead of over 3000!

However, that being said, you should be able to run a query right now that says [Activity2YesNo] = -1 (Yes) and [Confirmed2YesNo] = -1. Put Yes or -1 in the criteria for those two fields, and include the date fields in your query. You should get the answers you want.

When you get it restructured, you'll just have to add one more field to the criteria: [Activity] = "2"