Results 1 to 5 of 5
  1. #1
    whubbard is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    2

    Make Table Query - Run's ok when viewing the query but creates a table using primary key instead.


    Hi guys, Apologies, I've probably done some thing very dumb. Very novice user. I'm trying to use "Make Table" to create a new table based on a data entry table. When i click on "Datasheet View" on my make table query the data is displayed correctly. But once I run "Make Table" and open the new Table only the original table primary keys are displayed. How do i get the new table to display the "Names". It's a very basic table design. I have attached a word document to try and explain. I would really like the new table to reflect the names and not the primary key associated with the fields. Have googled but think i'm the only dumb bunny that has managed to stuff something so simple.
    Attached Thumbnails Attached Thumbnails Make Table.jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you using Lookup field in table? I NEVER do this. The field stores the key, not the descriptive name. If you want the name, will have to do a query that joins tables.

    Why do you need to make a table? Why not just use a query?

    Implementing a procedure that routinely modifies database structure is usually indicative of poor design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    whubbard is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    2
    Thankyou for your reply. I started again and followed your advice. It works now. I'm trying to design a roster system for our local footy association. In order to ascertain each team is allocated 16 games i need to transpose the Home Team and Away Team, i don't know how to explain why i do it, but in excel i used pivot tables but had to duplicate the games for the season and then transpose the home and away teams so there are 2 records for every game (we have around 1100 games for the season). I thought access would be useful as i can create a macro to "make table" and "append table" (transposing home and away teams). i've added a screen shot using test data that now displays the records correctly. Once the roster is entered, i can then create queries to check on missing data (teams that don't have 16 games, teams that don't play against each other, etc). I wish pivot tables were still available in access. There's probably an easier way to check a club's roster, but i don't have the expertise required.

    Thanks again.
    Attached Thumbnails Attached Thumbnails make table correct.jpg  

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This append query will add new records with away and home teams swapped.
    FYI, 'Round' is a function name in Access and as such should not be used as a field name, since under some situations, it might confuse the access compiler.

    To be clear, this query will duplicate every record in mytable with the new records having Home Team and Away Team swapped.

    Click image for larger version. 

Name:	Swap.JPG 
Views:	8 
Size:	53.5 KB 
ID:	33444

    The sql is:
    INSERT INTO mytable ( Round, Venue, [Age Group], [Away Team], [Home Team] )
    SELECT mytable.Round, mytable.Venue, mytable.[Age Group], mytable.[home Team], mytable.[away Team]
    FROM mytable;
    Last edited by davegri; 04-08-2018 at 04:13 PM. Reason: include sql

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I agree with davegri, append records to existing table. No need to repeatedly create and delete table.


    Also recommend no spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. How to make a primary key on a linked table?
    By MarcoFromDowntown in forum Database Design
    Replies: 2
    Last Post: 03-04-2015, 04:19 PM
  2. MAKE TABLE QUERY maintain PRIMARY KEY
    By taimysho0 in forum Queries
    Replies: 6
    Last Post: 12-15-2011, 09:22 PM
  3. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  4. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Tags for this Thread

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