Results 1 to 8 of 8
  1. #1
    tareyj8569 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    43

    Combine similar records

    Hey all,
    What I have been trying to accomplish is combine records into one that are similar but I just cant seem to figure it out.
    Shortly what I have is one table with records that can have the same location but data in different times, so I am trying to combine into single records.
    I attached an example since I am not the best at explaining, I was trying a few different thing with queries but was not getting any where.
    CombineTest.zip



    Thanks

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Sorry I don't download files. But in general, I assume in each record is a field with an ID value that is exactly the same in multiple records. You state 'the same location data'. Note the field must be exactly the same and not just similar.

    You don't say what you mean by 'combine'. If you are attempting to display the differing data side-by-side - then you need to go with cross tab type query. This is sort of an excel like approach. I myself do not recommend this because it is not how normalized data is stored and if there is a great number of records the side-by-side method runs out of room.

    Normalized data is in records (stacked) which seems to be what you describe and that is the best way to keep things.

    If by 'combined' you meant some sort of math - then that can be done in an Aggregate query using the current data structure.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need to separate this out into tables:

    table 1 for Locations
    table 2 for type
    table 3 for date and time (one field)

  4. #4
    tareyj8569 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Posts
    43
    Ok, so now I have a table for Locations which has a one to many relationship with the Data.
    When the Data gets uploaded it stamps a primary Key that matches by location (LTID).
    Click image for larger version. 

Name:	Sample1.JPG 
Views:	9 
Size:	22.6 KB 
ID:	26260
    When I open the Location Table it groups the data together as it should
    Click image for larger version. 

Name:	sample2.JPG 
Views:	9 
Size:	27.7 KB 
ID:	26262
    The question I have is how do I combine the records for each ID to show as 1 record if it applies(also by date of course) in a datasheet looking view.
    Example:
    From above instead of 2 Yellow-Aisle 1 should be one reading
    -----LT------------Date1-----1200---1300---1400---1500

    Yellow-Aisle 1 - 10/26/2016--12-----20------9------- 5
    Yellow-Aisle 1 - 10/27/2016--1-------3------ ------- 6

    Also included latest changes I have made if like to look
    CombineTest.zip
    Attached Thumbnails Attached Thumbnails sample2.JPG  

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When I see field names like 1200, 1300, 1400 and 1500 it tells me that the table is not normalized.
    What do 1200, 1300, 1400 and 1500 represent?

    Another thing: it is a bad idea to begin object names with a number. The Access Gnomes can get persnickety and cause weird problems.

  6. #6
    tareyj8569 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Posts
    43
    The 1200,1300,1400 and 1500 represent military time.
    The time the data falls under.
    Also to note the data would be getting imported from an existing excel document, so trying to make access take the data in and show right.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    By combining data and type into one field you are creating restrictions in the data and it definitely isn't normalized. Nor is defining all the times as separate fields on the table - that will come back and bite you in the future should any changes occur.

    If you only want 2 tables then
    1 - locations - (can be primary key or can be an autonumber)
    2 - data - three fields only: type, date, time value

    You would use a crosstab query to make it look like a spreadsheet.

    Don't be concerned about importing from Excel - import into a temp table and then have queries to update the tables.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Leaving your table in "spreadsheet" design (not the "correct" way to design a table) , I created a totals query that gives the results like you posted. (see attached db)




    It is important to understand that databases are not a larger excel - Excel tends to store data horizontally (short and wide) and combines data storage and presentation in the same view. Databases store data in tables (tall and thin) and uses queries, forms and reports for presentation.


    You might look at these threads for info on database design:
    https://www.accessforums.net/showthr...ghlight=models
    Post #3

    https://www.accessforums.net/showthr...ghlight=models
    Post #2 and #3


    Tutorials:
    http://www.rogersaccesslibrary.com/f...s_forum46.html


    Note: I renamed the table "Data" because "Data" is a reserved word in Access.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2016, 09:26 AM
  2. Combine similar data in report
    By pipoconanan45 in forum Reports
    Replies: 2
    Last Post: 09-17-2015, 12:04 PM
  3. Replies: 7
    Last Post: 06-19-2014, 12:00 PM
  4. Combine Similar Data From Three Queries
    By Kerberos in forum Reports
    Replies: 2
    Last Post: 05-02-2014, 11:27 AM
  5. If columns are similar combine?
    By westcoastbmx in forum Queries
    Replies: 0
    Last Post: 09-04-2009, 12:54 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