Results 1 to 8 of 8

Find cheapest in value for an ID in a query and add/rank data in new columns

  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    45

    Find cheapest in value for an ID in a query and add/rank data in new columns

    Hello

    I've got about 430000 lines of prices on different items I've combined this using a UNION query. I would like the find the cheapest distributor for each item.
    Basically my Union query is build as the example below:




    Varenummer Brand Price Stock Distributør
    C6035A HP INC. 106.4 271 Dis1
    C1823D HP INC. 309 2 Dis1
    DES-1008D/E D-LINK 61 3 Dis1
    C13S041264 EPSON 200 4 Dis1
    C13S041316 EPSON 50 0 Dis1
    C6035A HP INC. 140 271 Dis2
    C1823D HP INC. 300 2 Dis2
    C13S041264 EPSON 500 0 Dis2
    C13S041316 EPSON 699 2 Dis2
    C6035A HP 744 271 Dis3
    C1823D HP 100 2 Dis3
    C13S041264 EPSON 210 0 Dis3
    C13S041316xx EPSON 5 2 Dis3

    Now what I would like is to in new rows get it like this:

    Varenummer Cheapest price Cheapest stocklevel Cheapest distributor 2. price 2. stocklevel 2. distriubtør 3. price 3. stocklevel 3. distriubtør
    C13S041264 200 4 Dis1
    C13S041316 699 2 Dis2
    C13S041316xx 5 2 Dis3
    C1823D 100 2 Dis3 300 2 Dis2 309 2 Dis1
    C6035A 106.4 271 Dis1 140 271 Dis2 744 271 Dis3
    DES-1008D/E 61 3 Dis1

    So that I for each Varenummer get the cheapest distributor and list them, if they do not have it in stock, they shouldn't be counted, if they don't have an item at all, they should just be blanks in the cells. However if they have the item, they need to list the price, stocklevel and distributor name.
    Is this possible using a query based setup?

    Click image for larger version. 

Name:	Excel.PNG 
Views:	14 
Size:	46.0 KB 
ID:	36114

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    you can do it in a query (actually multiple complex queries) - but need to clarify A) what you want to happen when two distributors have stock at the same price. and B) what if there are more than three distributors with stock? C) What happens when no distributor has stock.

    Also, why do it this way? just because Excel pushes you in this direction because of the way Excel works, doesn't mean it is the right way to do it in Access - you are still not going to 'scan' say 150k rows in excel to find what you want. You could have a query to sort by product and price and exclude zero stock (why even bring it in to your union query if you are just going to exclude it later) then filter by product as required - ok, suppliers are listed one below the other, but pricing and stock are next to each other and not several columns off to the left, so I would argue easier to read.

    The fact you are using a union query implies your data is not normalised. If it was normalised, it is likely the final query will be simpler and more importantly, significantly faster. The union query prevents the use of indexing, which on 430k records will be significant - something that might take a couple of minutes with indexing could take hours without.

  3. #3
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    45
    Hej Ajax, thanks for the reply. Happy to supply all info as is needed

    I have upto around 10 different distributor files with prices in them, that will be updated regularly in the same location, therefore I've linked them into the database (afraid of bloat if need to have them as tables). The files comes in different formats CSV, TXT and are in no way similar. Which is why I've created a query that just lists those 5 columns in my previous post.

    As for the clarifications needed: For A it doesn't matter if their prices are the same, one of them should be moved into 2. column (which one doesn't matter). B I will add the same amount of columns corresponding to the distributor files i load into it (thought it best for the example to just show 3, not sure if that was smart or not ). C If no one has stock, it should just bypass that line entirely.

    After I've read what you typed, I will eliminate all the ones with 0 stock BEFORE my union query, so that parameter shouldn't be a problem there.
    The way I've been using other data, I would prefer to have it in columns as shown instead of rows.

    Perhaps this isn't easy solvable by query and easier with VBA? I don't have a preference, so if you think VBA would be much easier, then I will try that road, but any inspiration regardless would be much appreciated.

    Edit: Setting a criteria of stock >0 reduced the lines from 430000 --> 117000
    Last edited by FoolzRailer; 11-04-2018 at 10:41 AM. Reason: Added criteria result

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    no easier in vba and will be extremely slow

    Your basic query would be something like this to give you a ranking which you can use to determine cheapest, next cheapest, etc

    Code:
    SELECT A.Varnummer, A.Price, Count(B.Varnummer) AS Rank
    FROM UQry A INNER JOIN UQry B ON A.Varnummer=B.Varnummer
    WHERE B.Price<=A.Price
    GROUP BY A.Varnummer, A.Price
    next you will need to link this back to UQry on Varnummer and Price. If you have same prices this will give you a problem because they will produce duplicates. No way round this without a primary key. You might be able to create a fake one by combining distributor and price into one field, it won't be indexed but will remove duplicates

    from this you can now create a crosstab query, using the ranking as the column header and suggest first distributor(or the fake primary key) as the value

    finally you link the crosstab query back to UQry on Varnummer and distributor (one for each column) to bring back price and stock

    As I've said before, without indexing, without a primary key and presumably linking to csv etc files this will be extremely slow - perhaps take 24 hours, maybe longer.

    You might get better performance if you filter to only look at one particular varnummer at a time, the (excel) idea of having a massive list to pull out the one or two items you want to look at is not the database way.

    I strongly advise you normalise your data, use an autonumber primary key and index the price and handle bloat as a separate operation. Even then it will still be slow for the number of records you have.

    Whereas simple sorting and seeing the data vertically will be pretty much instantaneous with a normalised dataset.

  5. #5
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    45
    Quote Originally Posted by Ajax View Post
    no easier in vba and will be extremely slow

    Code:
    SELECT A.Varnummer, A.Price, Count(B.Varnummer) AS Rank
    FROM UQry A INNER JOIN UQry B ON A.Varnummer=B.Varnummer
    WHERE B.Price<=A.Price
    GROUP BY A.Varnummer, A.Price
    next you will need to link this back to UQry on Varnummer and Price. If you have same prices this will give you a problem because they will produce duplicates. No way round this without a primary key. You might be able to create a fake one by combining distributor and price into one field, it won't be indexed but will remove duplicates

    from this you can now create a crosstab query, using the ranking as the column header and suggest first distributor(or the fake primary key) as the value

    I strongly advise you normalise your data, use an autonumber primary key and index the price and handle bloat as a separate operation. Even then it will still be slow for the number of records you have.

    Whereas simple sorting and seeing the data vertically will be pretty much instantaneous with a normalised dataset.
    Hey Ajax

    I appreciate the time you've taken to spell this out for me and the step-by-step process! I understand that normalizing the data might be a must, though I will give this approach a go, and if it's too slow for what is needed I will revisit your plan.

    I'm having some difficulties with the Ranking, as I'm getting Ranks displayed as 20 and 25, but I only have 10 different pricefiles.

    Code:
    SELECT Join_distributor.Varenummer AS Varenummer, Join_distributor.Pris AS Pris, Count(Join_distributor_1.Varenummer) AS Rang
    FROM Join_distributor INNER JOIN Join_distributor AS Join_distributor_1 ON Join_distributor.Varenummer = Join_distributor_1.Varenummer
    WHERE (((Join_distributor_1.Pris)<=[Join_distributor].[Pris]))
    GROUP BY Join_distributor.Varenummer, Join_distributor.Pris
    ORDER BY Join_distributor.Varenummer;
    Query in Query_design window Antal=Count:
    Click image for larger version. 

Name:	RankQuery.png 
Views:	11 
Size:	23.8 KB 
ID:	36144

    Filtered to Varenummer called 2 (only one varenummer called that), with a rank of 25?
    Click image for larger version. 

Name:	Filtered to one varenummer called 2..png 
Views:	11 
Size:	10.5 KB 
ID:	36145

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    would need to see your 'source' data for Varenummer=2

  7. #7
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    45
    Quote Originally Posted by Ajax View Post
    would need to see your 'source' data for Varenummer=2
    Well you are right, something fishy in the dataset... The Varenummer 2 and for example 2CM-8N(BNS) is there several times in the sourcedata from the CSV. Can I just do a group-by first selection query on Varenummer --> First and get rid of it that way? It seems the issue for some reason is different values of stock but same price.


    From left to right:
    Sourcedata/Import - Ranking - Ranking linked back to distributors/union query.

    Click image for larger version. 

Name:	dis1.jpg 
Views:	7 
Size:	158.2 KB 
ID:	36168

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,313
    don't know what lagar is (value?). If you don't need it, exclude from your union query (or sum/first/whatever)- providing you are using a straight forward union query, that will eliminate duplicates. Otherwise create a new query - either group by or select distinct to exclude lagar and then base your query on that instead.

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

Similar Threads

  1. Best,cheapest and fastest method to share database
    By kinneretaccess in forum Access
    Replies: 10
    Last Post: 04-09-2018, 05:51 AM
  2. Replies: 0
    Last Post: 09-05-2017, 09:35 AM
  3. Replies: 2
    Last Post: 05-21-2017, 04:40 PM
  4. Rank Query
    By cbritt927 in forum Queries
    Replies: 2
    Last Post: 06-02-2015, 03:42 PM
  5. Correlated query to find cheapest manufactuer
    By rafnews in forum Queries
    Replies: 8
    Last Post: 03-27-2015, 10:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums