Results 1 to 8 of 8

Probably simple problem for experienced Access users

  1. #1
    Needaccesshelpplease is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    4

    Probably simple problem for experienced Access users

    Hey guys, I hope you can help me out with a small question. Before I explain my situation I have to say I'm relatively new to access and haven't discovered most of its possibilities yet (for example using SQL).

    Ok this is the situation: I have three database tables; The first database (X) is from 2010 with 1500 rows, the second database (Y) is from 2007 with 40.000 rows and the thirth database (Z) is from 2003 with 85.000 rows. All three databases have corresponding relationnumbers (and other information which concerns me like buildingyears, brands and types, but that isn't relevant for this question). So table Y has 1500 rows that are corresponding with 1500 of the 40.000 rows in table X, and table Z has 40.000 rows that are corresponding with 40.000 of the 85.000 rows in table Y. All three databases have the same format, but some information has been changed since 2003.

    Now I want to create a new table (or query) in which I get to see the most recent information per relationnumber. So, at the end, I need to have one table with 1500 rows of data from 2010, 40.000 rows of data from 2007 and 43.500 rows of data from 2003, as a matter of speaking. What should I do?

    I hope I've made myself clear and I would really appreciate it if someone could take the time to help me out and explain what I have to do step by step!

    Thanks in advance!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    ...and other information which concerns me like buildingyears, brands and types, but that isn't relevant for this question
    Actually it is pertinent.

    So you basically have overlapping data in the 3 tables. If you look at the records that are in more than 1 table, are the values in all fields (or at least the ones you care about) exactly the same? If so, that can be handled with a Union query of the 3 tables. If not, how do you determine which of the almost matching records you will want in the new combined table?

    Out of curiosity, what do you mean by relationnumbers? Can you provide some example data?

  3. #3
    Needaccesshelpplease is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    4
    The fields in all three tables are indeed exactly the same. It's just the data inside the fields that differ from eachother.

    The relationnumbers are the parcel numbers of buildings. Some example data would be like this:

    Field : relation number heating system brand and type building year boiler system brand and type building year
    Row 1: 10023993 AGPO 784130 2003 Intergas k89 2004
    Row 2: 10023994 AWB KJ89 1998 Remeha l03 2008

    Etc.

    Could you give me some general tips how to work with a Union query?

    Thanks for your quick response

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    A UNION query has to be constructed in the SQL view window. It would go something like this (I'm not sure what your actual field names or table names are but if you have spaces in them, then you will have to enclose them with square brackets). The number of fields in each SELECT statement must be the same and the fields must be in the same order:


    SELECT [relation number], [heating system brand], [type], [heating building year], [boiler system brand], [boiler type building year]
    FROM tableX

    UNION

    SELECT [relation number], [heating system brand], [type], [heating building year], [boiler system brand], [boiler type building year]
    FROM tableY

    UNION

    SELECT [relation number], [heating system brand], [type], [heating building year], [boiler system brand], [boiler type building year]
    FROM tableZ



    If the data returned in a record from one table exactly matches (all fields) that in another table, the UNION query will only return 1 thus eliminating any duplicates.

    Once you have verified that these are the records you want in the new table, then you will have to create an append query using the UNION query as it's record source for the new records.

  5. #5
    Needaccesshelpplease is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    4
    Jzwp11 thanks for your help. I tried your UNION query set up and it worked fine, but it wasn't exactely what I was looking for.

    I need to replace data, instead of listing them all beneath each other. So some rows of tableY and tableZ needs to be replaced by the rows from tableX (where the relationnumbers matches), and from tableZ all the rows needs to be replaced by the rows from tableY. And in that way I'm hoping to create one table with a total of 85.000 rows.

    A fictional set up of this final table would be something like this:
    data from tableZ (because relationnumber tableY and tableX are missing)
    data from tableZ
    data from tableZ
    data from tableZ
    data from tableY (because it replaced relationnumber and all the data that comes with that number from tableZ and relationnumber from tableX is missing)
    data from tableZ
    data from tableZ
    data from tableX (because it replaced relationnumber and all the data that comes with that number from tableY and from tableZ)
    data from tableY
    data from tableY
    data from tableY
    data from tableZ
    data from tableZ

    So just to be sure; every relationnumber is like an ID number, which is my main key for one row of data.

    Hope I've made myself clearer this time.

    Looking forward for your answer and hopefully solution

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    I'm a little confused.

    How may records were returned by the UNION query?

    So some rows of tableY and tableZ needs to be replaced by the rows from tableX (where the relationnumbers matches), and from tableZ all the rows needs to be replaced by the rows from tableY.
    What you describe above is different from what you said earlier. If that is the case, you will need update queries. So basically you want to overwrite 2003 and 2007 data with 2010 data.

    First, make a backup copy of the database.

    For the update query you will want to do the oldest first, include tableZ and tableY in a new SELECT query, join by the relationnumber field, add all of the fields from tableZ that need to be updated (do not include the relationnumber field). Change the query type from a SELECT to an Update query. You will probably have to edit the query text to make sure that the tableZ fields are the ones being updated by the tableY fields. Run the query

    Do the same for tableZ/tableX and then again for tableY/tableX.

    From there you should be able to use the UNION query as the basis for running an append query to create 1 new table.

    Another option to the Update queries is to add a calculated field to each of the subqueries in the UNION query that designates the year of the data 2003, 2007 and 2010. You could then run a query based on the UNION query that looks for records with the same relationnumber and counts them. If the count is more than one, you know you have more than 1 year's data for a relation number. You could then run a delete query to get rid of the older records leaving just the newest record.

  7. #7
    Needaccesshelpplease is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    4
    Thanks for your response! Sorry for my late response.

    It have been insanely busy here at work. To answer your question: The Union query returned 131500 rows of data, where I only needed 85000.

    I tried your select and update query and it probably would have worked, but after some thinking I came with a somewhat easier solution myself.

    I exported the tables to excell and used a Vlookup. I placed some landmarks behind the data (for example a column of '1') and told my Vlookup that for every data that matches, I want to see a '1'. After that I sorted the 1's and deleted the old files and placed the new ones in the excell sheet.

    So eventually my problem is solved.

    Thanks for your concern and help!

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    Glad to hear that you worked out a solution. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Very simple problem
    By alexc333 in forum Queries
    Replies: 8
    Last Post: 07-21-2011, 06:35 AM
  2. Simple Problem with Validations
    By oleBucky in forum Forms
    Replies: 11
    Last Post: 04-12-2011, 04:39 PM
  3. simple form problem
    By kcsun in forum Forms
    Replies: 0
    Last Post: 08-11-2010, 11:28 PM
  4. Access 2007 Users and Permissions Problem
    By botts121 in forum Security
    Replies: 3
    Last Post: 07-06-2009, 09:23 AM
  5. Replies: 1
    Last Post: 03-31-2008, 08:53 AM

Posting Permissions

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