Results 1 to 6 of 6
  1. #1
    jelto is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    3

    Design comparison database

    Hello.



    I trying to build my own database in Microsoft Access 2013. I have the following tables:

    Passanten (Table with objects, a passant is a type of healthcare)
    Zorgaanbieders (Table with different healthcompanies)
    Pas_zorg (Table with passantenID, ZorgaanbiederID, price2016 and price2017)


    Im filling the Pas_zorg table with passant prices from different companies. Now I want to compare the prices from company A to B etc. to see what company gives the cheapest "passanten".

    The problem: The table passanten has around 7000 unique records and companies dont give information about all the passanten. Example: Company A has 5000 passant prices and company B has 3000 passant prices.

    If I Compare both companies in total the result wont be correct because some companies have more prices and the total score will be bigger than company B. Is there a possibility to compare only records if they are both filled?

    Hopefully you will understand my description!

    Kind regards,
    Jelto Christiaens
    The Netherlands

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is there a possibility to compare only records if they are both filled?
    Depends on the scale of the problem but you can create a 'core' query something like

    SELECT *
    FROM Pas_zorg A INNER JOIN Pas_zorg B ON A.passantenID=B.passantenID AND A.ZorgaanbiederID<>B.ZorgaanbiederID

    This will provide every combination of prices from each healthcompany where a matching record existing in one or more other companies which you will need to refine further based on the price data (e.g. latest date) and can be filter for a specific company, healthcare type etc

    it is also possible your Pas_zorg table is not constructed correctly. It appears to be

    passantenID, ZorgaanbiederID, price2016, price2017
    1..................1.......................20.50.. ......22.00
    and should be

    passantenID, ZorgaanbiederID, priceYear, price
    1..................1.......................2016... .....20.50
    1..................1.......................2017... .....22.00

  3. #3
    jelto is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    3
    Perfect, will try it and let you know. The Pas_zorg is indeed not correctly. Will change it too. Thank you!

  4. #4
    jelto is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    3
    I think my description isn't clear. Let me try it with a different example:

    I have three tables:

    1. Shop
    ID,Name,Type,Country
    1,Supermarket A,Supermarket,Netherlands
    2,Supermarket B,supermarket,Netherlands
    3,Night Shop A,NightShop,Germany

    2. Products
    ID,Type,Name
    1,Fruit,Apples
    2,Fruit,Strawberry
    3,Soda,Cola
    3,Soda,Fanta

    3. Prices
    ShopID,ProductID,Year,Price
    1,1,2016,€10
    1,1,2017,€11
    2,1,2016,€9
    2,1,2016,€10
    2,2,2016,€5
    2,2,2017,€6

    Now I want to know who sells the cheapest fruit of all companies. In My data (Prices) it should be Supermarket B because the Apples are cheaper then Supermarket A. But if I combine them to a total score the price will be higher because Supermarket B has the prices available of the strawberries and Company A does not.

    So I want to exclude the strawberries if one of the two companies does not have data available. Is this possible?

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you write out the logic in sentences? Does year have anything to do with it? I presume that you want to get the latest price.

    Who sells the cheapest fruit would be apples/supermarket B, but I am sure there is more to it than that. Would you be doing all your buying from the supermarket that has the lowest overall score, or the most number of products, or would you buy different products from each supermarket?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my solution provides the basis for doing this. Now you have split the prices you can modify the query slightly

    SELECT *
    FROM Pas_zorg A INNER JOIN Pas_zorg B ON A.passantenID=B.passantenID AND A.ZorgaanbiederID<>B.ZorgaanbiederID AND A.Year=B.Year

    the entire query will only include items which are matched by at least one other supplier

    But I am confused by what you mean by a total score - what is the rule you are applying?

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

Similar Threads

  1. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  2. POS database design help
    By ghardwick in forum Access
    Replies: 1
    Last Post: 02-10-2015, 07:24 PM
  3. Working on a Cross Comparison Database
    By Mrdude1020 in forum Access
    Replies: 1
    Last Post: 07-23-2014, 12:19 PM
  4. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM

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