Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,774

    Don't just focus on what you just have now. What happens if you get a second customer?, are you going to create another db for them?
    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

  2. #17
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    No it will always be one customer

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,085
    But it may not forever be the same customer, in which case you still ought to have a customer table. At the very least, if the single customer changed their business name, you could easily keep old and new data related to the old and new names by adding the new entity instead of editing the old one.

    You don't design for what is, nor in truth do you only design for what may possibly be. You design for what is right for the business in keeping with entity/attribute relationships and db normalization.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,085
    But it may not forever be the same customer, in which case you still ought to have a customer table. What would you say if the current customer recommended you as a supplier? "Sorry, my database only allows for me to have one customer"? At the very least, if the forever single customer changed their business name, you could easily keep old and new data related to the old and new names by adding the new entity instead of editing the old one.

    You don't design for what is, nor in truth do you only design for what may possibly be. You design for what is right for the business in keeping with entity/attribute relationships and db normalization.
    Last edited by Micron; 08-20-2022 at 06:40 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #20
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Hello, thank you for your long term advice. I know you are right, I am not used to think or work with database, I've been reading about normalization theory, and if I want to do it like what you are suggesting, I wouldn't know how to link the data between tables. If you could take a look at the excel in my previous replies, you would see that there are barcodes in every tables, so how to link them? How to link data from sales2020 and sales2021 with march2022 and stock data? there are no IDs to link them except the actual unique barcodes. This is the problem that I needed help with.

  6. #21
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,085
    Orange tabs are changes I made to a sheet or sheets created to show potential design elements:
    - database as a table name is like having basket and volley balls and naming them "gym". I changed to tblProducts
    - tblGrandshows: orange text shows one way; red text shouldn't be there. Did you review About calculated table fields - http://allenbrowne.com/casu-14.html ?
    - tblGrandShows2 shows what is really the proper way to normalize tblGrandshows & requires tblSalesType. Unbound combo on a form, using salestype field for the combo list is how you get the salestypeid value into the grandshows table. Not important I guess, but it seems like an odd name for a table and probably imposes limited use - something which I think you haven't hit yet.

    Red tabs are sheets that should not be there. Apply the concepts of normalization to those elements, assuming you need them at all. I do not understand your business so I cannot be too specific with design suggestions. The idea of having both orders and sales is odd to me. If I order 10 do you not sell me 10? If you can only provide 8 then that seems like orders table would have OrderQty and ShippedQty fields where you can then either ignore shortage or later make up the shortage. That info would also enable you to query what products were short and maybe that would reveal a pattern or problem.

    Never avoid making a table that supports normalization regardless of how few records it will hold. I have used tables that would only ever have one record in order because it maintains normalization. The importance of this is how easy it becomes to expand, especially for unforeseen circumstances. Having to add fields (e.g. sales year) is a sure sign of bad design and is called repeating fields (each field is about the same thing regardless of the year difference). Such information goes in records.

    Further to the last paragraph, it could be said that tblProducts could be further normalized. IMO, currency is an entity; i.e. it is it's own "thing", and USD is not an attribute of the table (Product). USD is an attribute of currency, therefore you should have a currency table. However, orders and/or sales is where you'd use the currency pk values, not tblProducts. Same can be said for Price, but that potentially raises another issue. You must be careful how you use records from a price table. You don't want prior sales/invoices etc. being affected by a price change. That is another topic you might want to explore if you don't know how to ensure that doesn't happen.

    Lastly, watch your spelling. You'll be wondering why things don't work when you type currency and you spelled it wrong in the object name (field, table, whatever). There are a few spelling errors.
    projectdatabaseMicron.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Hey Micron did you send me a pvt msg, it seems I lost it.

    Your work is amazing, let me say that in this project I have one customer and I want to know what he orders, and what he actually bought, that would be my sales, and what he sold, because I also want to add his sales, so Orders are his orders, sales are my mine, and sold is what he sold. I sell to someone and this someone also sell to various customers.

    my question is how to link products to grandshowtable, let's say I had 50000 books there, wouldn't be hard to link them?

  8. #23
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,126
    Hi

    Micron has given you an example in Excel so I believe you should now make a database using the example shown.

  9. #24
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,085
    I'll review your comments tomorrow. Are you sure that you want to worry about what your customer sells? It seems odd that you would care. Let's say I was selling you 100 tons of steel each year. I would not care how much of that you re-sold, lost as scrap due to defects, lost as theft, stored in warehouses, turned into saleable product, etc. I'd only care about what you order and that you continue to order.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #25
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,774
    Would the customer even want to hand over that information?
    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

  11. #26
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,085
    I suppose this would show you customer orders vs your sales but I have no idea where you're getting your data from regarding the sales of your customer. Frankly, I would not bother with that, mainly because you cannot rely on the accuracy of that data, nor reasonably expect it to remain forthcoming. Besides, I don't see why you should care but that's secondary.

    Your form would have a calculated control (textbox) where the controlsource would be =[OrderQty]-[Shipped] but you don't store that result. This could also show where you need to ship shortages later on, assuming that's your intent.

    ProdIDfk SalesYear OrderQty Shipped
    1 2020 25 23
    2 2020 26 26
    3 2020 27 27
    4 2020 28 22
    my question is how to link products to grandshowtable
    tblProducts was your grandshow table, renamed and redesigned so there's no linking required in the manner of your question. A link between product and orders (or whatever you want to call it) is important though. To clarify, I don't see a need for both orders and sales tables when you can quite nicely get by with one or the other. Since you seem to be concerned about short shipping, I'd stick with tblOrders as a sales indicator. That way, your sales are what you ship for your orders and not what you were supposed to ship but didn't.
    Last edited by Micron; 08-22-2022 at 08:35 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    Irunit is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2022
    Location
    Placeless
    Posts
    24
    Thank you so much.

Page 2 of 2 FirstFirst 12
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