Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24

    Help with foreign keys

    Hello,



    This is my first post ever on access.

    I am unable to find a foreign key between tables to set up a relational database. So I ended up having the same primary keys for all the tables, which didn't work well when creating queries. here are the tables details, feel free to adjust the tbls orders:


    tblDatabaseinfo: ProductBarcode Title Author Price Currency discount etc..


    TblStock: ProductBarcode (as primary key as above) Stock Sales Jan Sales Feb etc...


    TblOrders: ProductBarcode OrdersJan Ordersfeb etc...


    How can I link these tables, will the barcode be the primary key for them and not create problems when my database gets larger?


    I'm not an expert in access so I would appreciate any tip.


    Thank you..

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Welcome to the forum.

    For more info on relationships

    Since you are new to database, I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary identified in my signature link Database Planning and Design.

    If you scan through the materials at that link, you will find other references to videos, articles, tutorials etc.

    You should also review Normalization.
    Good luck.

  3. #3
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Hello and thank you!

    I will certainly study the resources you mentioned. However, what would a quick reply be for my question above?

    Thank you

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    From your description you haven't set your tables up correctly.
    Hence orange pointing you to having a read on normalisation.

    Your tables should be something like tblProducts, tblClients, tblOrderHeader, tblOrderDetails

    Each table should have it's own unique Primary Key field - so looking at the Order Header Table it would have

    ORDER_ID (PK), Client_ID_FK, OrderDate, ClientPONumber

    Now we have your Order Details table

    OrderLine_ID (PK), Order_ID_FK (this is the Order id from the header table above, ProductCode_ID_FK ( this would be the Barcode in your example, but its NOT a prmary key in this table.), OrderQty, LinePrice etc etc


    None of your tables should have columns like Stock Sales Jan Sales Feb etc... You have a single date field and use that to group and present data later in queries.
    That is spreadsheet design and does not work in databases.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There is no answer to that question if you're going to be concerned about normalization. Your design suffers from Excelitis i.e. designing like a spreadsheet. Your current plan is to have repeating fields to say the least and perhaps unrelated fields that belong in some other table. Hard to say more without knowing more about the purpose of the db. Best that you learn normalization and then come back because it is fundamental and crucial to success. I could post just links for normalization but I know that you could benefit from all of these if you care to do all the research. These can save you a lot of grief later on.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Thank you so much for all your thoughtful replies.
    This forum is such a blessing...

    I will dive into the materials you guys gave me and hopefully my next posts would reflect a much better plan for the database I want in access 2016.

    Thx a lot!!
    Stay safe..

  7. #7
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    I see now why my virtual information was a bit misleading for you.

    I hope this would make things easier to handle or deal with.

    Let us say for this project, I only have one Customer, and just books to sell, and I don't care about adding employees. Just want to build a database to everything this customer has bought, or will buy in future. I also don't care about orderID or dates, what matter to me is their date by month, say March2022 he bought 10 copies of this book and 15 of another book. then in may2022 he bought 25 copies of something else. etc..by months. I don't care about OrderID to add it, no need for that.

    How then may I find the links between tables? all the vidoes you shared with me has multiple orders and multiple employees and customers, I am dealing with another issue here. I have one customer and one product (books to keep it simple) so they only have unique barcodes but there a are lot of them.

    How should I think about this database? and the goal is to run a query that could tell me for example the title and author and order and sales for this barcode at march2022.

    So that's why I thought of adding barcodes a unique primary key since there are no way to link Orders with sales, since orders are what he actually wanted but sales are what he ended up buying, since he can order 10 copies then only buy 8 in real life. SO i want to keep a record of what he orders and another for what I sold him.

    is it possible to build a database for such goal?

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Mike60smart

    Hi

    When dealing with the process of a Customer on a given Date who Purchases a number of items, you
    would need at least 4 Tables

    Customer
    CustomerPurchaseDates
    PurchaseItems
    Products

    See the Relationship Diagram for the Links between the Primary Key in 1 Table to the Related
    Foreign Key in a Related Table.

    Click image for larger version. 

Name:	RI.JPG 
Views:	25 
Size:	46.0 KB 
ID:	48542
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    I see, I only have one product, books, what is the difference between: PurchaseItems and Productstbl?

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Mike60smart

    Hi

    Well the tblPurchaseItems is where you would store the Primary Keys of the items from the tblProducts.

    See the attached database for how to construct the Forms for Data Input.

    The Matching Coloured Controls show the Main Form PK and the related FK

    Customers.zip
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Purchase Items would be like order items, several books that the customer purchases.
    The products (books) that the puchase would be selected from the Products table?

    Most people would think of it as Customer who makes an Order, which has OrderItems in it, and the Items they ordered are stored somewhere to be selected.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    projectexcel.zipThank you so much for your time and for your much appreciated help.

    I still have this feeling that I am being misunderstood, so I just build a project on excel to show you what I am dealing with with only few barcodes.

    Take a look and I am sure you will change the structure of what you are telling me to do.

    Everything in this excel and in the data base will be for one customer. I just don't know how to link sheets or tables, this was my initial question based on this file..

  13. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Do you only have 1 Customer?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Mike60smart

    Using your Excel file I would suggest that you need to look at Allan Browne's example Inventory database.

    Your tables would be along the lines shown in the attached example.
    Attached Thumbnails Attached Thumbnails RI.JPG  
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Thank you so much! I will stand on this sample to go on, yes only one customer and only books. (just like excel, a lot of barcodes and other data)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Foreign Keys
    By RustyRick in forum Access
    Replies: 1
    Last Post: 04-19-2020, 03:56 PM
  2. Indexing Foreign Keys?
    By skidawgs in forum Access
    Replies: 6
    Last Post: 12-10-2019, 11:52 AM
  3. Foreign keys
    By Accessuser67 in forum Access
    Replies: 1
    Last Post: 10-05-2015, 06:33 PM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 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