SSWPD Table Data Capture and SSWPD Select Query Creation

[rmoreno]

I have numerous table files all have the same field names and all beginning with the same five characters (SSWPD).


I need help in reading each table, including the table name and putting all the data into a single select query (qry_SSWPD).
Can anyone help? Thanks.

[Dal Jeanis]

Need more info to be sure what you want. Sounds like a UNION query against the numerous table files.

Select "SSWPD_TableA" As TableName, Field1, Field2 etc
from SSWPD_TableA
UNION
Select "SSWPD_TableB" As TableName, Field1, Field2 etc
from SSWPD_TableB
UNION
and so on

[rmoreno]

Using your suggest method requires that I individally input each table name. I would like for the code to read ALL of the tables and only select the ones that start with SSWPD.

[Dal Jeanis]

Ooooo - a challenge. Well, then you'll have to use VBA to build the query. The query itself will look as above, but the method for building it will be something like this...

First, you'll need to get a list of the tables. This pages has some explanation about that
http://www.ehow.com/how_6680641_acce...-database.html

You'll end up with something like this:

SELECT MSysObjects.Name FROM MSysObjects
WHERE MSysObjects.Type = 1 AND MSysObjects.Name Like "SSWPD*";

Then you'll have to pull the column names of the table. That's described pretty fully on this next reference, as a matter of fact, the code above would slip right into the code on this page - http://stackoverflow.com/questions/4...e-in-ms-access

Then you'll have to build your UNION. You'll need to validate that each one of the tables does, in fact, return the same columns in the same order. Might have to sort them by name, or just save the original name order and use them over and over again.

Honestly, if I was trying to do any real work with this Union table, and if it wasn't too huge, I'd probably just create a temp table and append all those items, rather than make a UNION. But, your mileage may vary.

[rmoreno]

Wow, there's alot of information to take in here. Let me take a moment to read and understand the content of each hyperlink and see what I can take from them. Given my inexperience in code writing this may take awhile. Is there no simpler way of accomplishing my goal?

[Dal Jeanis]

I know what you've asked for, and it isn't difficult, but it also isn't trivial. However, I don't know if there's an easier way, because I don't know exactly what the purpose behind your goal is. Let me ask some background questions:

1) Is the database large (does it push the 2G limit of Access)? Is it volatile in data (does it change often and are the changes critical)? Is it volatile in structure (does it add new tables like these all the time)? Is the database data sensitive in nature (is there private information in it)? Is the database structure sensitive in nature (would even the structure of the database give away private information)?

2) Are you putting this data all together once and then archiving/deleting the individual tables? Alternatively, will the individual tables continue to be updated, requiring continual update of the big query/table?

3) Do you want to do this once, or do you need to do it over and over again? If you want to do it only once, then you only have to do each step once, and you can do them manually, without programming in any error checking. Error checking is the most complicated part of programming a job like this, in a volatile database.


Your options are:

(A) If you are doing this consolidation once and then discarding the dozens of individual tables, then you can run the process manually rather than coding it. That's a lot easier, and I can walk you through the work one step at a time.

(B) On the other hand, if you need this function to work automatically, and you will be executing it repeatedly, then you'll have to get it coded somehow. If the database is not sensitive in structure, then you can create a new blank database, import four or five of the tables, delete all the data from those tables except a couple of fake records, then post it up here, and we can walk you through coding it. (You wouldn't even have to delete and fake the data if the data wasn't sensitive.)

That's the kind of questions I'd ask and options I'd give, knowing nothing about your business and your true goals.

[rmoreno]

Here are the answers to the above post response:
Q: Is the database large?
A: No. The entire database is about 13MB.

Q: Is it volatile in data:
A: Data is updated multiple times during the day.

Q: Is it volatile in structure?
A: New tables are added periodically.

Q: Is the databae data / structure sensative in nature?
A: No.

Q: Are you putting this data all together once and then archiving / deleting the individual tables?
A: No.

Q: Will the individual tables continue to be updated, requiring continual update of the big query / table?
A: Yes.

Q: Do you want to do this once, or do you need to do it over and over again?
A: Over and over again.

I have also attached an reduced version of the database to help clarify the table structure.

Thanks.

[Dal Jeanis]

OK, then there's no easier way. We'll have to code the solution. However, since your database is small, we can use appends to a temp table, rather than building a huge union.

Do you need the consolidated table to be up-to-the-minute, or is this a periodic thing?

FYI, If you need it up-to-the-minute, it would be smarter to permanently merge all the tables and change whatever is using the current tables to use queries.

[rmoreno]

Up to the minute.

[orange]

Just curious--
Can you tell us what the SSWPD represents and why there are multiple tables with the same prefix?
Perhaps if we knew more about WHAT you are trying to do, other options might be available.

I think Dal is on the right track, but am curious as to why the various tables exist; their purpose; and why they are processed together now?

[Dal Jeanis]

Yeah, it's begging for a redesign: with many identical tables, and needing to refer to them all UNIONed together all the time, it looks like mission creep has reached critical mass. The records all appear to be tracking the results of various destructive testing on manufactured goods. I can't think of a business reason that would force these different but similar items to be kept separate from each other, given their identical structures and usage.

My prescription: (1) merge all the tables, retaining a reference to tablename as a separate field (2) delete all the individual tables (3) if needed by your current architecture, create "view" queries that are named the same as the prior tables, so that all the forms and reports will automatically work as expected (4) update the "add" forms, to put a bound hidden field that defaults to the appropriate tablename.

It might have other advantages as well, depending on whether you are currently having to create a new screen for each table - those could all be consolidated to a single form, with a drop-down to select the process type ("tablename").

[rmoreno]

Sorry. The table naming is the result of equipment programming and cannot be changed in any way. That is what I'm having to work with.

[Dal Jeanis]

Different question, then. How is the data getting into the tables? If we can detect the update to the table, we can clone the update into the combined table.

[rmoreno]

Proprietary equipment software. Cannot be changed.

[Dal Jeanis]

And a second question - how squishy can you make that "up to the minute"... is 15 minutes delay too long?