Results 1 to 4 of 4
  1. #1
    HiddenStar is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2014
    Posts
    1

    Creating a full dataset table with basic data points

    Hi all,

    It's nice to see that there is an active forum for people who need help with Access

    I would like to seek the expertise and opinion of the fellow users here on how I can use Access to build a full dataset in a single table based on skeletal/basic data points? Here is a picture to explain what I mean:

    Click image for larger version. 

Name:	Dataset.png 
Views:	12 
Size:	95.8 KB 
ID:	18503



    So basically, the table on the left is the one with basic data points in the various columns. The one on the right is the final result after the values in the various columns are duplicated for each possible combination for all the columns. I understand that this can be done easily in Excel but albeit, manually. I would like to know if this can be accomplished in access as I experience the need to change the basic data points constantly, making it troublesome to do in Excel.

    Many thanks in advance

    HiddenStar

  2. #2
    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,716
    You should start with understanding the differences between Spreadsheet/Excel and relational database/Access.
    Here are a few links to help.
    Database Principles
    Database Design

    Good luck

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    The way to do what you seek in a relational database such as Access is to make A, B, C each its own individual stand alone table. Then EFG is a table - when you add field E into it while in table design view - you option it to be a Look Up field type and point it to table A; repeat for field F to point to table B, and field G to point to table C.

    This automatically makes E,F,G fields to be drop down selectors which will give the user all the list in their respective table. If you update A - then you will see the new info offered in E.

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Have you tried below & seen if it gives the results as required ?
    Code:
    SELECT 
    tblCountries.Country, tblShops.Entity, tblPLLines.PLline
    FROM 
    tblCountries, tblShops, tblPLLines;
    Thanks

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

Similar Threads

  1. Replies: 6
    Last Post: 10-22-2022, 08:27 AM
  2. Exclude data points where fields are equal
    By jysupert in forum Queries
    Replies: 1
    Last Post: 09-02-2014, 03:30 PM
  3. Basic question on table design data type: lookup
    By drspanda@gmaill.com in forum Access
    Replies: 1
    Last Post: 07-17-2014, 07:56 PM
  4. Replies: 2
    Last Post: 06-24-2013, 12:37 PM
  5. Replies: 3
    Last Post: 12-04-2012, 01:09 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