Results 1 to 9 of 9
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    DB design same unique ID between two systems

    Hello,



    So I have two systems with same ID field.
    For example field A is ID and field B is it’s description.

    I am putting together information from two System but with same ID.

    How can I design it without losing the system of where the data comes from and without redundancy ?

    Please advise.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not really clear on what you've got, but a UNION query can pull them together and you can add a source field:

    SELECT Field1, Field2, "Table1" As Source
    FROM Table1
    UNION ALL
    SELECT Field1, Field2, "Table2" As Source
    FROM Table2

    Omitting ALL will eliminate duplicates.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Ohh thanks for the answer pbaldy but I am designing a database from scratch. Not pulling information from an established database.

    So I have two system with info. Both can have simialar primary key and it’s description.

    I want to break those data into different schemes with good database design, however still can know which primary key comes from which system.

    Say I have a file say from system A and it have 50 fields. It has a primary key and is unique. Then I have system B and it has 401 fields and it has primary key and is unique but the two fields from the two system can over lap. I broke the file into many different tables.

    Each of those tables can’t not be join to the other tables without the primary key being on all of them.

    How can I design a database that is concise and still know which system the database and its primary key comes from ?

    Hope you know what I meant.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Johnsieto,

    Please tell us about the proposed application in simple terms. Give us some examples of the type of information involved and what is done with that information. It's difficult to focus advice when dealing in the hypothetical/abstract.

    By system are you suggesting tables? Rarely have I see a table with 50 fields, and never one with 400.
    Please clarify your terminology.

    Good luck.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    By system meaning data from table A is extracted from one system call it software dbA and table B is extracted from software dbB.

    50 and 400 is just a number. Basically table A is a data dump and and table B is also a data dump. It can be 1000 fields from either table or software db.

    In simple term - I want to create a database, designed a schema that intergate data from the two system however still be able to tell what data comes from what software db.

  6. #6
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I think it needs a database design expert ?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm still not clear on things, but as you import from each system you can populate a field in your db with the source.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    be aware that access is limited to 255 fields in a table or query, so if your source systems have more, you have a problem.

    you are providing conflicting information - these two statements for example

    but I am designing a database from scratch. Not pulling information from an established database.

    ...
    By system meaning data from table A is extracted from one system call it software dbA and table B is extracted from software dbB.
    if you want real help, provide some real background.

    think it needs a database design expert ?
    they will need the real background

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Thanks but I don’t know how it was designed in the extracted database. I will try to populated a field as it goes in the database.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-21-2012, 11:10 AM
  2. Operating systems
    By nashr1928 in forum Access
    Replies: 3
    Last Post: 12-05-2011, 03:24 PM
  3. Student Information Systems
    By DrCreosote in forum Access
    Replies: 1
    Last Post: 09-29-2010, 06:33 PM
  4. systems tables
    By ldbeeman in forum Access
    Replies: 2
    Last Post: 09-18-2010, 05:42 AM
  5. MS-Access in 64 Bit operating systems
    By todd in forum Programming
    Replies: 2
    Last Post: 08-26-2010, 04:25 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