Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51

    SSWPD Table Data Capture and SSWPD Select Query Creation

    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.

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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

  3. #3
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    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.

  4. #4
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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:
    Code:
    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.

  5. #5
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    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?

  6. #6
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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.

  7. #7
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51

    SSWPD Table Data Capture and SSWPD Select Query Creation

    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.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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.

  9. #9
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    Up to the minute.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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?

  11. #11
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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").

  12. #12
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    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.

  13. #13
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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.

  14. #14
    rmoreno is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Apr 2013
    Posts
    51
    Proprietary equipment software. Cannot be changed.

  15. #15
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    And a second question - how squishy can you make that "up to the minute"... is 15 minutes delay too long?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-23-2012, 12:46 PM
  2. Capture data from TCP port
    By todster in forum Access
    Replies: 0
    Last Post: 07-19-2011, 12:22 AM
  3. Capture Form Data to Export to Excel
    By ajones92 in forum Forms
    Replies: 8
    Last Post: 06-16-2011, 03:35 PM
  4. Replies: 2
    Last Post: 01-31-2011, 08:31 AM
  5. Select Data from SQL into an Access table
    By MichaelC in forum Programming
    Replies: 4
    Last Post: 07-28-2010, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums