Access 2002 Query with No Results

[Guest]

I am using Access 2002. I am trying to make corrections to 13 tables within one database. Each table contains 150 fields named the same. It has multiple clients and other duplicate data that I have entered within 8 of the corresponding fields several different ways. I am trying to copy and paste, or find and replace corrections without opening each table. I have tried to create a query that returns with no results, or returns with errors. I have combined all the records into one table, but cannot figure out how to link (?) them within the same database to all the tables. I have created just one relationship and/or multiple (8) relationships with each of the tables, but this does not seem to help. How do I do this? Can anyone tell me what I am doing wrong?

 

[John Vinson]

I am using Access 2002. I am trying to make corrections to 13 tables
within one database. Each table contains 150 fields named the same.

I'm sorry... this database is DREADFULLY non-normalized.

I've needed as many as 60 fields in a table... twice. And storing 13
identical tables is MUCH less efficient than having one big table,
with an additional field to distinguish the values.

It has multiple clients and other duplicate data that I have entered
within 8 of the corresponding fields several different ways.

Another indication that you really need to think about restructuring
your tables! If you have eight duplicated fields, you should have TWO
TABLES (or more!) in a one to many relationship. This would let you
enter the data *ONCE*, and link it to the non-repeating data using a
Query.

I am trying to copy and paste, or find and replace corrections
without opening each table.

You can create 13 Update queries, one for each table.

I have tried to create a query that returns with no results, or
returns with errors. I have combined all the records into one table,
but cannot figure out how to link (?) them within the same database to
all the tables. I have created just one relationship and/or multiple
(8) relationships with each of the tables, but this does not seem to help.
How do I do this? Can anyone tell me what I am doing wrong?

You're using Access as if it were a spreadsheet. IT ISN'T. It's a
relational database!

Stop. Step back; learn about the concepts of "Normalization" and
"Relational Design". There are lots of good books and web references -
start with the Microsoft Knowledge Base at
http://support.microsoft.com, and the Access Web at
http://www.mvps.org/access.

You need to identify the "Entities" - real life persons, things, or
events of importance to your application; each type of Entity gets a
table. You then need to identify their Relationships.

Since I know absolutely nothing about what you've tried or the nature
of the data in your tables, I can't be specific - but you're quite a
ways down the wrong track! If you'ld post some description of the
data, we'll try to help you find your way back out of the woods.

 

[Guest]

Thank you for responding. I realize this is quite frustrating. I have been working with this problem for several years. I may not be using the correct Access terminology to explain this situation. Please be patient with me. This is job related. I have no choice in the number of tables. The tables are committed (imported) from a Cardiff program. Each table is exported into SAS at different times. Although there are 150 fields in each table, there are only 9 (I miscounted), fields I need to alter from each table in this procedure. This is all historical data. I am trying to correct (use one full name throughout all tables, with spaces), fill in blanks, and correct ID’s according to Part Type, without opening and closing all 13 tables repeatedly. My data has nothing to do with products, pricing, etc.
Example of the Eight Fields I need to use:
Field 1-Name (Text, field size 255),
Field 2-Person’s ID (Number, field size Double),
Field 3-Vehicle Type (Text, field size 255),
Field 4-Vehicle’s ID (Number, field size Double),
Field 5-Vehicle’s ID (Text, field size 8)
Field 6-Company (Text, field size 255),
Field 7-Company’s ID (Number, field size Double),
Field 8-Part Type (Number, field size Double),
Field 9-Voucher Number (Number, field size Double) although this number should be unique, there are occasional duplicates.
I hope this explains my situation a little better. I have already visited http://www.msps.org/access and read the Ten Commandments. I realize I have broken several. I will be researching “Entitiesâ€, “Normalizationsâ€, and “Relational Design†while waiting for my miracle. Thank you again for your help.

PS
I am new to Newsgroups. Thanks for the tip on cross posting, although I don’t know how to do that either. And what or where is the “Newsgroups line of your newsreader�
Jeri

 

[John Vinson]

Thank you for responding. I realize this is quite frustrating. I have been working with this problem for several years. I may not be using the correct Access terminology to explain this situation. Please be patient with me. This is job related. I have no choice in the number of tables. The tables are committed (imported) from a Cardiff program. Each table is exported into SAS at different times. Although there are 150 fields in each table, there are only 9 (I miscounted), fields I need to alter from each table in this procedure. This is all historical data. I am trying to correct (use one full name throughout all tables, with spaces), fill in blanks, and correct ID’s according to Part Type, without opening and closing all 13 tables repeatedly. My data has nothing to do with products, pricing, etc.

You may be in REAL trouble here. Access tables have a limit of 255
fields, so you're ok there - but also a limit of 2000 bytes *ACTUALLY
OCCUPIED* in any one record. If the sum of the lengths of these 150
fields adds up to 2000 or more, you'll get an error.

Would it be *at all possible* to *store* the data in a normalized
(tall-thin) table structure? You can still massage the imported data
into normalized form, and export from a Query combining data from
multiple tables into SAS; it is *not* necessary to have the data
non-normal just because other applications are not normalized.

Example of the Eight Fields I need to use:
Field 1-Name (Text, field size 255),
Field 2-Person’s ID (Number, field size Double),
Field 3-Vehicle Type (Text, field size 255),
Field 4-Vehicle’s ID (Number, field size Double),
Field 5-Vehicle’s ID (Text, field size 8)
Field 6-Company (Text, field size 255),
Field 7-Company’s ID (Number, field size Double),
Field 8-Part Type (Number, field size Double),
Field 9-Voucher Number (Number, field size Double) although this number should be unique, there are occasional duplicates.
I hope this explains my situation a little better. I have already visited http://www.msps.org/access and read the Ten Commandments. I realize I have broken several. I will be researching “Entities”, “Normalizations”, and “Relational Design” while waiting for my miracle. Thank you again for your help.

You don't say how you want to "alter" these fields, but I would guess
that the only way to do this is to create thirteen Update queries, one
for each table. This won't be all that hard to do - create one, open
it in SQL view, copy the SQL into WordPad or another text editor, and
replace all the tablenames with another one of the tablenames. Copy it
back into the SQL window of a new query, save it, and go on to the
next.

You can (once you've saved the queries) run them all from a Macro or
from VBA code.

Just as a concern - among the rules you're breaking are the use of
Doubles for IDs. Doubles are Floating Point values and therefore are
approximations - I'd be VERY leery of using a Double for a PersonID,
because you might have PersonID 42.3 not matching PersonID 42.3
because one of them is actually 42.299999999999993 and the other
42.30000000000004. Text, or (if the fields have no fractions) Long
Integers would be safer. You may be stuck with that though!