3 Replies - 1138 Views - Last Post: 13 July 2014 - 04:25 PM

#1 cddean  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 13-July 14

product price comparison across multiple stores

Posted 13 July 2014 - 07:18 AM

Hey everyone,

I am trying my hardest to learn Microsoft Access 2010 but keep hitting these hiccups to which I can't find solutions on Google.

I am building a database for trade routes and cant figure out how to calculate the max difference between each product over all the different stores that sell that product.

I have the database setup as such:
tblProduct
PK ID
ProductName

tblStation
PK ID
StationName

tblProductStationRelation
PK ID
FK StationNameR
FK ProductNameR
ProductPrice

I would like to be able to tell it which Station I'm at, and it tell me which, out of the available products at that station, is the most profitable and where to go sell it.

Eventually down the road, I would like to also create a little AI to calculate most profitable trade routes between all Stations and have it build the path. (i.e. (Station1, Flour) > (Station3, Corn) > (Station12 > Eggs) > etc...)

Thanks guys!

Is This A Good Question/Topic? 0
  • +

Replies To: product price comparison across multiple stores

#2 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 668
  • View blog
  • Posts: 2,942
  • Joined: 03-December 12

Re: product price comparison across multiple stores

Posted 13 July 2014 - 02:35 PM

What have you tried so far?

Quote

I would like to be able to tell it which Station I'm at, and it tell me which, out of the available products at that station, is the most profitable and where to go sell it.


These are where clauses for the most part, top() for the most profitable, and the last bit I am confused over. What criteria would you have to select a good location to sell?

An alternative approach is to incrementally add what you are looking for while gaining understanding. A database can't exactly tell you where you are located, however, it is just the data holder after all.
Was This Post Helpful? 0
  • +
  • -

#3 cddean  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 13-July 14

Re: product price comparison across multiple stores

Posted 13 July 2014 - 03:48 PM

I am a C++ and Java programmer so you will have to excuse me if I am attempting to use concepts that are unachievable in SQL. I cannot even seem to get a variable created in SQL without Access through red flags at me.

The criteria for best location to sell would be the best profit margin, which is the highest difference between each item and all other like items being sold by other stores.

The current location is the only input field in a form. The query should read the field and do a comparison of each product with all other stations and store the highest difference (profit).

Psuedo code:
currentStation = field input
var profit = 0
bestStation = null
bestProduct = null

array PRODUCTLIST = select all products from currentStation
forloop (durection of PRODUCTLIST)
array STATIONLIST = select all stations that contain productIndex
forloop (duration of STATIONLIST)
var temp = STATIONLIST[stationIndex]
if (temp > profit)
profit = temp
bestStation = STATIONLIST[stationIndex]
bestProduct = PRODUCTLIST[productIndex]
endif
increment stationIndex
endforloop
increment productIndex
endforloop
return Station

Example:
Station1 has:
milk $3
eggs $4
cheese $2

Station2 has:
milk $2
eggs $5
bread $1

Station3 has:
bread $3
eggs $3
cheese $3

User inputs current location as: Station2

query looks at Station2 milk at $2
query pulls list of stations with milk (Station1 & Station2)
query compares prices of Station2 milk with all in the list ((3-2) > 0? , (2-2) > 0?)
profit set to 1, bestStation set to Station1
query looks at Station2 eggs at $5
query pulls list of stations with eggs (Station1, Station2 & Station3)
query compares prices of Station2 eggs with all in the list ((5-4) > 0? , (5-5) > 0?, (5-3) > 0?)
profit remains 1, bestStation remains Station1
query looks at Station2 bread at $1
query pulls list of stations with bread (Station2 & Station3)
query compares prices of Station2 bread with all in the list ((1-1) > 0? , (3-1) > 0?)
profit set to 2, bestStation set to Station3

Sorry about all the complications, I was hoping there was a way to achieve this without having to go learn SQL, but I may have to spend a couple months and dive into it...
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3459
  • View blog
  • Posts: 11,708
  • Joined: 12-December 12

Re: product price comparison across multiple stores

Posted 13 July 2014 - 04:25 PM

Quote

I am a C++ and Java programmer

.. and you've never had a need to learn SQL? I find that a little surprising.

Yes, you can build queries in Access' Design View but, as you are a programmer, you should embrace the opportunity to learn SQL and add it to your CV; it is a VERY important skill that most employers look for, or require.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1