Associating Fields

[clickster]

I have a table that tracks tape backups for multiple locations. There are 65 locations. The table consists of a single [Date] field, a single [EnteredBy] field, and 130 fields (2 per site). We'll start with the first site, whose site name is "001". It's two fields are [001a] and [001b]. [001a] is an option group with values of 1-4 and [001b] is a memo field for taking notes on the backup. Here's the delimma. I need a query that, for a specified date, will return each "a" field that has a specified value (1-4) AND its corresponding "B" field. It's the AND part that's getting me. I can return all the "A" fields with a value of 2 or something like that. What I can't figure out is how to tell it that, when you return 034a, also return 034b.

A couple of side notes:
The sites are all 3 digits or 3 digits followed by a letter (i.e. 009b) but they are not in nice, sequential order (i.e., there is not a 010, 019, etc.) so any sort of programmed loop couldn't rely on that.

I plan on creating a report based on this query. How can I determine in the report which site an "A" and "B" field are associated with?

I realize this is a bit convoluted, so I am open to any suggestions on how to change the overall layout of what I'm doing.

Thanks in advance for any help

 

[dhookom]

You are "committing spreadsheet" with your 130 repeating fields. Can you normalize your data so that each record contains only 4 fields? The fields would be BackupDate (Date is a function so don't use it as a field name), EnteredBy, BackupStatus, BackupNotes.

This provides much greater flexibility since you don't ever have to add more fields if you add locations.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[clickster]

The problem is that normalizing the data in that way would require a person to manually enter 65 records one-by-one. The other way, while I realize it doesn't follow typical DB normalization rules, allows us to have a single form on which they can enter data for all locations. Every day, at least 60 of the 65 locations have a value of "1" in their "A" field, so we can simply default them to that, allowing the person to only have to enter data for the other 4 or 5 along with notes for those 4 or 5 rather than creating 65 individual records. The way that you're suggesting would be much easier for me to build and query, but the idea of entering 65 separate records has already been ruled out by those above me.

The only other way that I can think of is to have a form with the fields for all 65 locations on it (one in each row) and have the form submit each row as a separate record. Is there any way to do this?

 

[lespaul]

Check out this article on JeremyNYC's website that explains normalization (an un-normalized database is another word for duane's comment of 'committing spreadsheet'! Good one, duane!!)

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

Leslie

 

[dhookom]

There is absolutely no reason for having to manually enter 65 records one-by-one. You should create a table of locations with a LocationID primary key field. Then you simply run an append query based on this table with the proper date and other default values as desired. Display these records in a continuous form or subform and there is no more data entry than you would have in a non-normalized table structure.

You will have the added benefit of being able to add new or inactive old locations without changing tables, forms, queries, reports, code,...

I personally would not go any further without normalizing but it's your application. "committing spreadsheet" comes from a fellow MS Access MVP Jeff Boyce.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.