Unanswered: Compare two fields and update a third field
I have just spent the last 4 hours trying figure out this problem and still can not find a solution. There are a number of other similar posts regarding this, but I have not been successful in transferring the ideas in my database. Here is what I have going on:
3 Tables Products, PriceList, and Stock
They are pretty self explanatory. I have separated these tables because I get an updated price list and stock report from a csv file on a regular basis.
All three tables share two columns, ItemName and Model. Also, Products and PriceList share a column Price and Products and Stock share a column Stock.
What I would like to do is to have an update query set the Price in Products for matching items (WHERE Products.ItemName=PriceList.ItemName AND Products.Model=PriceList.Model....etc) from the Price in PriceList. Same thing for Stock, update Product.Stock from the Stock.Stock.
It sounds to me like you're trying to make a mountain out of a mole hill.
You can import from csv into specific fields if that's your reason for seperating these tables.
How many individual fields have you got in these 3 tables?
If you don't feel comfortable pulling data from a csv into specific fields, just set up a 'tmp' table, import the csv data from there, then use a script to open a recordset, and 'INSERT INTO' your one main table. Then you can manipulate the data there without needing to worry about various joins.
If you can consolidate your data, avoiding repetition, that is nearly always preferred. Having eight relationships setup between three tables sounds extremely excessive and like you'll have a huge headache before the day is over.
I figured I was making this too complicated. The reason I wanted to separate the files is because the csv files do not have any index and are not in the same order.
The table Products, is going to be my main table and will keep records of my inventory. Right now I am still adding fields to this table, but the main ones for our purposes are as follows:
The csv files are generated to deal with a specific item type (I will be selling other products). These are for Magic The Gathering Online Digital cards. I run a third party program to buy and sell these "cards" online. It automatically exports a csv every couple of hours that shows the current inventory of these cards. Then I also receive a text file that I have to format with updated prices. This is what the data looks like from each file
(Price Text File) *Changes every 48 Hours
01 000 01 000 001 5DN C Abuna's Chant# 0.019 0.028 0.004 0.007
01 000 01 000 002 5DN C Armed Response# 0.018 0.028 0.004 0.007
I only need certain parts of this, and once I am done importing, regrouping, removing white space etc, I get the following:
Model | ItemName | Price | Special Price
5DN | Abuna\s Chant | 0.019 | 0.028
(Stock csv file) *Changes every 4 Hours
ItemName | Stock | Model | Card# of Set | Premium (Special price)
Blackcleave Goblin 11 SOM 54/249 No
Blackcleave Goblin 1 SOM 54/249 Yes
This one has a couple of interesting things. First of all is the Card # of set. For my purposes I really do not need to track this, but could use this as a identifier when importing. However, there are a ton of different sets, and therefore multiple card number 54. Lastly the Premium field shows whether or not a card is foil, which to complicate things further would use the special price. Also I realize the cards do not match (each from the top and shows my dilemma that they are not in order)
Lastly, I have access to a database that will dump all information about these cards and their pictures. I was planning on just dumping this into the product table, and having the csv's fill in the price and stock data.
Hopefully this clarifies the problem. Thank you so much for your help!
I see what you're trying to achieve now, and personally I would use just one table, with the four fields (along with any other you've decided to implement).
I'm not quite sure what your question is really. Your OP you were asking about joins, which if all the data is held in one table, isn't an issue anymore.
Multiple tables should only really be used, if say, you are recording a list of companies, and for import/export tax, you need to know where each company is based, and which economic area they're located.
Instead of having one main table with 'aCompany 1', located in 'aCountry 1', which is part of 'aContinent 1', which would (after inserting a lot of data), just end up with thousands of 'aCompany 1', with thousands of matching, never changing 'aCountry 1' and 'aContinent 1'.
Soooo, to cut down on repetition in the table, you would make a second table, maybe called 'ref_companyLocations', to hold the data 'aCompany 1', 'aCountry 1' and 'aContinent 1', then you would use a query, with a join (inner or outer, depending on what data you wanted to return), so whenever it found 'aCompany 1' within the 'main' table, it would know where to find 'aCountry 1' and 'aContinent 1', without needing potentially thousands of other records held in the 'main' table.
Does that make sense?
Like I said, I'm not really sure what question you're asking now, but hopefully that little bit of information has given you an insight as to when you'd want to use multiple tables. Which from what I can see of your potential setup, isn't necessary.
Also, I understand what you're trying to do, but why are you trying to do it? Why do you need the access database, and what do you hope to be able to achieve/do by having it?
I guess what I am ultimately asking is how to accomplish this. I have limited experience with dbs and php, but most of my programming background is in C/C++. I know the one table idea is preferable, I just did not know a way around having multiple tables. I have gotten close with my previous method, but keep getting errors and from what I can tell there is not much of a debugger. The two table idea would work just fine and I have done things like that in the past. I am assuming that I just need to do an inner join with that secod table and my csv/text data that I am importing so it has the SKU or primary key field that matches.
The main issue I am having is that the price list, while it will match the ItemName and Model, it does not match any index, namely the SKU. I could probably match the cards with the right price to get the initial inventory set, but am confused on how to do this once the prices change. These same issues apply to the Stock list, especially when that changes.
To answer you second question, which sorry I did not make more apparent I need to formulate this data to upload to a stand alone website. I can do this very easily one time, but as soon as everything changes I would rather have the database do the work for me. I am also hoping at some point to have this completely automated.
Thanks again for your time with this. It has been haunting me the last few days and I really appreciate your efforts.