denormalized database returning no data

[kate007]

Hi, I have inherited a highly denormalized ACCESS database where almost all information stored is in the one table - it's massive, almost all the other tables are look ups. Because muliple fields in this table relate to one particular look up table, when querying in design view I have to put multiple copies of the look up table in the query (and link each copy of the look up to the respective field) in order to get definitions of each of the fields that relate to the look up table.

This works okay if I just want to enter selection criteria based on just one of the fields (that is linked to one of the multiple copies of the lookup) - but when I put the same criteria in each of the fields the query does not return any data at all.

Help? ANY suggestions/comments would be greatly appreciated.

 

[Maquis]

When you put the criteria in each of the fields are you ANDing them or ORing them? If you're accidentally using ANDs then that may be your problem. (ANDs are on the same line in the query grid and ORs are on multiple lines.)


BTW, you may want to invest a little bit of time and re-design the database to be more normalized. Trust me, I've been there and it will save you time in the long run if you are going to be responsible for maintaining the database from now on. Just be sure to lock the user who designed the thing in the first place from tampering with your code when you're done. Otherwise, they'll see all the cool things you've done with it and want to "improve" on the design a bit. Maq
<insert witty signature here>

 

[kate007]

yes, I am using 'or'. I wish I could modify the database, however, we have all our reports running off it (and hopefully we will be moving to a data warehouse situation soon..). Thanks.