Using ID from different copies of a db to relate queries

[hilian]

In a case-reading study, there are 185 fields for five readers to fill in. Our IT department gave each reader a copy of the db to use. Each db has a slightly different name that includes the reader’s initials, e.g. QPR2_MA, QPR2_EF, etc. To bring the data together, I need to go into each reader’s copy and copy the data onto an Excel spreadsheet—I’m using Excel to analyze the data and create the report.

I thought it would be faster and easier if I could create a query to collect all the data in one place. I’ve done that—I linked each individual table to a db I created for the purpose, and a set up a union query to put them together. Because of Access’s 255 field limit, I had to create five union queries, one for each section of the data, and join them with a select query. Each record has a case number, which is what I used to keep the records together in the select query, but the case number isn’t always available to the readers, in which case, the readers use 999999, which means that if there is more than one of these, Access won't be able to put the parts of the record together accurately in the select query.

I would like to use the ID field to relate the queries, but because there are five dbs, the numbers are frequently the same. In the linked tables, In the queries created from the original linked tables, Access gives the ID field a different name based on the name of the original db, MainDataQPR2_ID_MA. There need to be queries because the 185 fields are divided into four tables. If I use the ID field, will Access distinguish between the ID numbers that come from the different original dbs, or will it treat them as duplicates?

Thanks,

Henry

 

[plog]

Lot's of confusing and ambigous info in your post:

185 field, but then bumped up against a 255 field limit? How?
Then you talk about 4 fields to hold the 185 fields. Why?
You mention linking via ID field, but didn't describe what ID field is.

So to answer your question--yes if you link via ID field and ID field is the same in any tables that involve that join they will be joined, regardless of what table/database that value is in.

Honestly, this sounds like a near-impossible task because you didn't start out with a properly structured database system. If you could post some sample data to demonstrate your delima maybe we can help or at least understand it more.

 

[The_Doc_Man]

You can join the records with matching case numbers but exclude the "999999" case with a WHERE clause " AND (CaseNo <> 999999 ) " (if the case number is numeric), or use '999999' if it is text.

Your 255 field limit should not have tripped you up on the union query unless you were somehow incautious with the names. The solution would be to assure that you don't try to recover data you don't need for the summary information.

However, it would seem to me that you should be able to have an extra database and write a MACRO to run some code to create your link to each table, then do an INSERT INTO from the individual copy's table to the working copy's table, then drop the link. Then do that again for each other individual copy. What is left is the same as a UNION query but as a single table. This sounds formidable, but it is repetitive, so if you solve the problem once, you just copy it as many times as needed, changing names to protect the innocent each time, and then when done, you can do your analysis inside a single database file.

The problem of sorting out the "999999" cases is on you because we don't know enough about your setup to know how to dig you out of that hole that you dug for yourself with that design flaw. (Allowing unidentifiable records to exist, much less to require import, is a questionable design.)

 

[hilian]

Plog,
My understanding is that in a union query Access counts the number of fields from each table/query against 255 field limit. That would mean 185 x 5 = 1275 fields. If I’m wrong, I did a lot of work for nothing.

The reason for four tables (not fields) to hold 185 fields is that each table holds a set of fields that make up a module, not all of which are used in each reading. As we add new modules we will exceed the 255 field limit. Right now, we’re using four, but we have three more ready to be added, and there are more on the way.

The ID field is the primary key. I’m using the record number that Access creates. Since there are five copies of the db, and they all start with record # 1, there are a lot of duplicates in any set of records from a particular reading, hence my question: in a union query, is 1 from qryQPR2MainData_MA different from 1 from qryQPR2MainData_EF? I think you are telling me that it is different, in which case, I can use the ID field to relate the different pieces of the original records as I put together the separate union queries using a select query. If I’m wrong, then I’ll have to set up a convention for readers to supply case numbers when they are missing, rather than just use the 99999.

Doc Man,

The db was intended as a data-entry tool rather than a relational data base. Originally, I thought I’d be putting together the pieces manually when I needed to create a spreadsheet for data analysis, and I’ve done that several times. Then I realized I could save time by creating a query to do it. The query, or rather the set of queries I described, works, except for the one problem of a small number of missing case numbers. I need every data record, so I can’t ignore the ones without case numbers, and I have to either find a way to make sure that each record has a case number or find another number to use. The Idea of a macro to put all the data is intriguing. I don’t have the time to work on it now, but in the future, especially as we add more modules, I think it’s exactly what I’ll need.

Many thanks,

Henry

 

[jdraw]

Perhaps I've missed some key factors, but if I had to receive 5 copies of the same structure of 185 fields, populated by different users and those sources were identified (filename was unique). And some CaseNumbers which were "not available" were valued with 99999, I'd try something along this line.
[And as I said --I may have missed key points in your scenario]

Create a table with your 185 field structure. Add a field for the UserName or ID (available from the filename), add an UnqRecordId (autonumber) to give each incoming record a unique numeric ID.
Use an Append query to add all records from first file; then second....etc.

You can query the CaseNumber for 99999 and get the user who did the input/identify the source. Then take whatever action you would do to assign a proper CaseNumber. You could set up a procedure to log that assignment--who/date/time. Repeat for all CaseNumber 99999s.

You end up with a single table of entered data that now has valid CaseNumbers and you can identify the source of each record in the table, and each record has a unique ID.

Just some thoughts for consideration.

 

[hilian]

jdraw,

Your suggestion is a whole lot simpler than what I tried to do. If I had only thought of it, it would have saved me hours of work, and since I would simply be appending records, I wouldn't need to worry about the 99999s since that field wouldn’t be involved in joining queries. I don't use that field for the analysis, so if I'm only appending, duplicates don't matter. As it is, the queries are working. In my original description of the problem, I left out a piece of information that I saw as extraneous: there are actually two kinds of case numbers. They are used for different purposes, and for one of these purposes, one of the fields isn’t always filled in. I was trying to get a series of queries that would be all purpose, which was why I had the 99999 problem. I settled on creating two sets of queries, one for each purpose, and that took away the missing case number problem.

I decided to try your suggestion anyhow to see how it would work in the future, and I ran into a new problem. Since the data in the original copies are in four tables, I have to put the records together. That’s why I used queries. To join the parts of the each record, I used the main key field, the auto-number that Access creates. When I tried to make a table from one of the (select) queries, I got the message that Access can’t make a table with more than one auto-number field. I have the tables that feed the queries linked on the main key/foreign key fields, and, to be sure that the records were being put together correctly, I included the foreign key fields in the query. Is that the problem? If I took out the foreign key fields, would there only be one auto-number field and would Access be able to make a table that I could append to?

Many thanks,

Henry

 

[jdraw]

As I said I may have misunderstood the post. In my scenario you had 5 complete sets of data to be acted on. It appears you had multiple partial records????

 

[hilian]

Multiple partial records is exactly the problem.