Showing only fields w/ certain values for each record 2

[clickster]

I need to figure out how to do this in SQL
each record in my table has the following fields:

Date, EnteredBy, 001a, 001b, 002a, 002b,...,065a, 065b.
(yes, that's a LOT of fields - but it's necessary)
Each number (001, 002, etc) corresponds to an employee

The "a" fields have a value of 1-4. The "b" fields have notes about why the a field has been given that value.

What I need is for the query to check a record (by the date that I provide) and return only the fields "a" and "b" fields where the "a" field has a value of 2.
So let's say that 014a, 026a, and 040a all have a value of 2. I want to see the following show up in the query:

date enteredby 014a 014b 026a 026b 040a 040b
Basically, I want to know what employees had a value of 2 and why they had it.

Is this possible in SQL? I'm teaching myself SQL right now, but I have not gotten to anything that tells me how to do this.
Any help will be greatly appreciated. Thank you.

 

[dhookom]

I find it hard to understand why you state "but it's necessary". If your table could be properly normalized your SQL would be very easy. Since your table structure commits serious "spreadsheet", you are going to have serious work to do either with a huge union query or code.

Duane
MS Access MVP

 

[JonFer]

I don't think it is worth your time to try to get any useful results out of this table. You are talking about 65 ORs in the where clause or looping across the 130 fields with VB code. Normalize and you'd be done in less than a minute with your query.

If you are getting the data this way because it is entered in a spreadsheet and the user is unwilling to change the format, there is a pretty simple way to use Excel to reformat this to a more normalized format before you pull in the data. There is also a way in Access to create the normalized version of your data using a separate table. Let me know if you want more information on how to do this.

John

 

[clickster]

If there is a way to normalize this, I would be greatful to hear how I can do it. I'm sure there is a way, but I haven't been able to come up with one. I'll give you the whole explanation of the table:

We have 65 offices who run backups each day. The "a" field for each is actually an option group with values of 1-4 that correspond to successful (1), failed (2), cancelled (3), and other (4) depending on what happened with that day's backup. The "b" field for each office is a memo field for notes on the backup. I need a setup where I can query for all of the "failed (2)" values that occur in a day and only display those "a" and "b" fields where the backup "failed". I can't have the user enter data for each office one at a time because that would be too tedious. I wouldn't mind if there was a seperate record for each office with that office's "a" and "b" fields, so long as the user doesn't have to enter data for office 1, click "save", enter data for office 2, click "save, etc.

Is there a way to normalize this data and still have a user-friendly way of entering the data?

 

[JonFer]

Do you have an Access form which shows one record at a time? The record represents one day and the data entry person selects the backup result value and enters the description for each office, right?

Here is what I would do. Make a new table with a primary key of BackupDate and OfficeID. The other two fields are BackupResult (index this with "Duplicates OK&quot and Explanation. Load this table from the current table via append queries, some VB code, or a macro loop.

On your form, set the Default View to "Continuous Forms" to display multiple offices at one time. When the user is ready to enter the backup info, they enter the date in a field on the form header and press a button that runs an append query to add 65 rows to the table with the new date and the OfficeID already populated (use a separate table that lists the 65 offices to do this). Only show the records for that date.

If you think it is not too dangerous, you could even default the BackupResult to "1" to save them having to key in the most common result - then you can make the field required, too. They would just need to change the BackupResult on the problem offices.

A simple query against the BackupResult and BackupDate gives you the information you want.

 

[dhookom]

Great response JonFer. Deserves a star.

Duane
MS Access MVP

 

[JonFer]

Duane - Thanks for the acknowledgement.

John

 

[clickster]

I'll have to figure out how to do the loop. I'll be starting on my VBA book as soon as I finish my SQL book. But that tells me where I need to get to. Thanks for all your help JonFer. I'll post back when I get it done and tell you how it went

 

[JonFer]

If VBA is completely new to you and you're not into programming, Excel can be used to normalize your table too. I imagine you have just a few hundred records so it wouldn't be too bad.

VBA is a powerful tool, though, so learning that will help you a lot in the long run.