I asked a question similar to this before but the data I am working with is a bit different than i first thought. I have several tables which are linked to excel sheets which have supplier quotes for differing categories. I have created a query which takes only the supply name and supply price for each vendor then shows the total price for each category (Total row below all records). I want to put the cheapest vendors prices all into one table. Show I have one table with all supplies and the price for which we will purchase them at.
populate a table with cheapest supplier
- Thread starter DampFloor
- Start date
- Local time
- Yesterday, 22:21
- Joined
- Feb 28, 2001
- Messages
- 30,806
OK, first a "standard disclaimer and warning." If the Excel sheets come to you externally and that is what you have to work with, OK. Just understand that Access limits what you can do with a table bound to a spreadsheet. If you could import the spreadsheets into a single table where your category acts as the qualifier / differentiator of each sheet's data, then you could start this with an SQL Aggregate query.
Or something similar to that sort of query. There ARE other ways for you to do this and if someone offers you something else, they are probably not wrong, just providing a different style.
If you can get that query to show you the values you want as a SELECT query, then you can convert it into a MAKETABLE query. (Converting a query from one type to another is an icon bar or ribbon function.)
Now, one reason I kind of pinged on the idea of multiple Excel spreadsheets is this: In order to get this to work correctly, you need to somehow unite those multiple spreadsheets and the way to do that in a single action, a UNION query, has limits on the number of unions. It is not clear to us as to how many sheets we are looking at. The alternative would be to manually import the Excel sheets into a common table (which would be my recommendation).
Code:
SELECT SUPPLY_NAME, SUPPLY_PRICE, VENDOR_NAME
FROM {name of aggregated table}
WHERE SUPPLY_PRICE = MIN(SUPPLY_PRICE)
GROUP BY SUPPLY_NAME;Or something similar to that sort of query. There ARE other ways for you to do this and if someone offers you something else, they are probably not wrong, just providing a different style.
If you can get that query to show you the values you want as a SELECT query, then you can convert it into a MAKETABLE query. (Converting a query from one type to another is an icon bar or ribbon function.)
Now, one reason I kind of pinged on the idea of multiple Excel spreadsheets is this: In order to get this to work correctly, you need to somehow unite those multiple spreadsheets and the way to do that in a single action, a UNION query, has limits on the number of unions. It is not clear to us as to how many sheets we are looking at. The alternative would be to manually import the Excel sheets into a common table (which would be my recommendation).
Hi Doc,
Right now i am working with 23 sheets. This number is also likely to go up. Unfortunately, all sheets seem to have slightly different formatting however they do seem to all have the same or at least similar fields, they just go by different names. There is no field in any sheet which states the product category or supplier, these are only noted in the file name. What I have done so far is linked each sheet and made append queries for each one which has criteria to remove unwanted rows. Could I then append all the filtered tables to a new table which I would contain all quotes, then make the price calculations from there? I would need to attach a category and supplier name to each record in this new table to perform the calculation.
Right now i am working with 23 sheets. This number is also likely to go up. Unfortunately, all sheets seem to have slightly different formatting however they do seem to all have the same or at least similar fields, they just go by different names. There is no field in any sheet which states the product category or supplier, these are only noted in the file name. What I have done so far is linked each sheet and made append queries for each one which has criteria to remove unwanted rows. Could I then append all the filtered tables to a new table which I would contain all quotes, then make the price calculations from there? I would need to attach a category and supplier name to each record in this new table to perform the calculation.
That is something i was completely unaware of, but could be quite handy. is it possible to make a field named "category" and "supplier" have the data in the file name populate these fields?
Yes. What is the naming convention for the files and do you have any control over it? If it is possible you won't be getting files with consistent names I would suggest parsing the name and prompting the user to validate.
Were it me I would have both suppliers and categories stored in a lookup table such that you can ensure only those values you expect are loaded. If a file comes in with a supplier or category you are not expecting, prompt the user (combobox) for the correct values.
I am not entirely certain of the naming convention. This is a school project which I am producing for a third party client. My plan is to have a folder which will contain all quote sheets. The database will be linked to each one if these sheets. I was just going to have the client change the name of the sheets they receive to whatever the database is linked to.
If i create a lookup table for category and vendor, can append each individual quote to a table with category and vendor included in additional fields? I believe from there I would be able to identify cheapest supplier per category and create one table which has the quotes at price which will be ordered.
If i create a lookup table for category and vendor, can append each individual quote to a table with category and vendor included in additional fields? I believe from there I would be able to identify cheapest supplier per category and create one table which has the quotes at price which will be ordered.
