Database for Metal Market Prices to be used in another database

[connerlowen]

HI,

I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets. There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?

Thanks,

Conner Owen

 

[plog]

I don't really understand the dilema. A properly structured database should be able to accomodate price updates at whatever frequency you want to gather them.

The really confusing piece is when you talk about an order to storing data. There is no order on a table, you simply throw data into records and records into the tabel. Order exists only when you explicitly tell the database to apply an order to a table (e.g. via query using the ORDER BY clause) when you retrieve data from a table.

So, can you provide your price table and then try and demonstrate what issue you forsee with this new method?

 

[connerlowen]

I know the database can accommodate updates to a table. My boss wants to store the prices for each metal every day. He then wants to select a date to quote by which will find the price for a certain metal and market on that date and use that number for the calculations. Can this all be done in a database? regrettably I am not at work therefore I cannot upload anything until tomorrow.

 

[plog]

Yes, this sounds pretty simple actually.

 

[connerlowen]

I can upload some stuff tomorrow to this thread. Do you mind taking a look at it and helping me implement this into my database?

 

[plog]

Sure. Just post it whenever you get a chance.

 

[connerlowen]

Will do. Thank you.

 

[JLCantara]

The metal market place is most probably on the web (is it?). If possible, can you join as many of those sites? In the past (well I am an old timer), I created an app that was reading the NHL sites. Unfortunately, a fire wiped out my lap, its contents and of course the backups (stupid to keep them at home...). I would like to recreate that procedure, even though some people call it scornfully 'scrapping' a site...

Good luck, JLCantara.

 

[connerlowen]

My current table structure used for metal prices is as follows.

table name: OptionMetalsListF
MetalsID - AutoNumber (PrimaryKey)
PreciousOrBase - Text (PreciousMetal or BaseMetal)
Metals - Text (Names of the Metals)
LME - Number (Price for Market LME)
COMEX - Number (Price for Market COMEX)
LMBA - Number (Price for Market LMBA)
EnglehardFab - Number (Price for Market EnglehardFab)
EnglehardBulliun - Number (Price for Market EnglehardBulliun)

I have a table NewMetalT with the following structure.
MID - AutoNumber (PrimaryKey)
PartID - Number (ForeignKey) on the many side of a one to many relationship
PreciousOrBase - Text (PreciousMetal or BaseMetal)
Metal - Text (Names of Metals)
MetalMarket - Text (Name of the MetalMarket Supplier Uses)
MetalPrice - Number (Price for Metal in MetalMarket)
SupplierMarkUp - Number (Percentage for MarkUp)
MetalWeight - Number (Weight of metals Precious in grams, Base in Kilograms)
MetalIncluded - Yes/No
NaecoMarket - Text (Name of the MetalMarket Naeco Uses)
NaecoMetalPrice - Number (Price for Metal in MetalMarket)
NaecoMarkUp - Number (Percentage for MarkUp)

There is a form that uses combo boxes to write data in the NewMetalT table. The prices are called from the OptionMetalsListF based on the combo boxes. I would like to add a date field to implement this function. Any ideas on what the best way to do this is?

Thanks

 

[plog]

I don't understand how those are to work to hold price updates.

1. You've got 2 tables, but both have price fields, why?

2. Both have a Metal field which is text. Is that how you link your tables?

3. Are these tables linked?

4. For each price, you should have a new record in a price table, not a different field.

I really can't get my head around how these hold price information in any useful way. I keep coming back to price being in both tables--very wrong.

Here's a barebones structure of how a price update system should work:

Metals
MetalID, autonumber, primary key
MetalName, text, name of metal
MetalPrecious, yes/no, Yes=Precious, No=Base


MetalPrices
PriceID, autonumber, primary key
MetalID, number, foreign key to Metals
PriceDate, date, date price of metal is for
PriceSource, text, tells where price was retrieved from (e.g. Naeco, LMBA, etc.)
MetalPrice, number, actual price of metal

 

[connerlowen]

I understand the structure and I will implement it. I will let you know how it goes and if I run into any problems.

 

[JLCantara]

Attached is a sophisticated answer to your issue. Since the person entering prices will have to use 2 pcs or go back and forth to find metal prices, I am suggesting an auto display of the web page corresponding to the market/metal.

Hope you will like it, as for me it was fun to create...

Good luck, JLC

 

[connerlowen]

I have began playing with the database. I really appreciate it. Knowing that this information is needed for calculations in another database I have a question. Should I implement the database that you made as its own database and try to get mine to pull information from it, or should I put the tables, queries, and forms in my current database? Thanks again, Conner Owen

 

[plog]

If mine is to replace what you currently have, I would try and integrate it in. First, I might have it work seperately to make sure I could get it to work like I want.

 

[connerlowen]

I am almost ready to test my current Database. If I have my tables and relationships correct it should work. As of right now, I am going to keep the metal prices separate to see if my current DB is working. If I can get it to work properly, then I will make a copy of it and try to integrate the metal prices. Thank you again for all of your help.

 

[JLCantara]

Plog idea is A1!

CL: do you like the idea of auto display of market web page?
On my laptop, it worked fine but after an MS round of updates, I get an error AFTER the page is properly loaded. Is it working fine on your PC???

 

[connerlowen]

It is not working on my end. That idea would be awesome if I can get it to work. However I only have 6 weeks left of my internship. I would like to have 2 or 3 weeks for testing and optimization and changes if possible. that being said, I need to have the entire database finished in 3 or 4 weeks and I am stressed to the max and doubting my ability to finish it in the given time frame. Back to the database you created. I am currently using a small table with the prices in it that are not stored by date for my calculations. I would really like to integrate the Metals Prices Database. Thank you again for all of your help. Without this forum I would have no chance of ever finishing this project.

 

[JLCantara]

Are you getting the full page then an error message? Could it be that LME pages have some fancy process that creates a problem?
Did you try the Market pages form? Here, copper works fine but platinum has a security problem. Don't work on the web browser issue: I will take care of it.

Suggestion: implement your tables as you set them, adding then record date. Then create simple forms. That should not take you more than half a day. Once the browser is fixed, you could use my App as an auxiliary and once it runs OK then had it to your app (that's step by step development...).

 

[JLCantara]

I FOUND THE SOLUTION!!!

This new webbrowser is totally different and of course undocumented...
It has no navigate function: rather one has to use ControlSouce = "..." to set the URL! and it works fine!!!!

I will clean up the app and post it later today (later for me means afternoon)

Celebrate! JLC

 

[connerlowen]

thank you so much. I really appreciate it.