Slickest way to duplicate queries with 'replaced' SQL content

[jdraw]

Not an issue. I haven't worked with Amazon, but was interested in the xml/vba code.

As for your algorithm, its much like anything
--what are the key things
-what is your goal
-what facts do you have access to
-what things can you guess/approximate
-what ultimately triggers your decision
...bla...bla

Are you content with your sales of XX on site Y?
You mention other sellers' feedback rating, delivery time,....

 

[peskywinnets]

The VBA was a real slog for me (I'm not a coder at all), if you're curious, you can download the (author unknown) Excel VBA that I heavily relied upon to get it working in Access https://docs.google.com/file/d/0B7gzonuQsNbvUFFlOXVLUFNpMms/edit (click on the down arrow at the top of the screen to download)

The VBA code essentially constructs a very long URL, like this (which has the api name & products of interest embedded in it)...
https://mws.amazonservices.com?&ASINList.ASIN.1=B002KT3XQM&AWSAccessKeyId=AKIAEXAMPLEFWR4TJ7ZQ _
&Action=GetLowestOfferListingsForASIN&MWSAuthToken=amzn.mws.4ea38b7b-f563-7709-4bae-87aeaEXAMPLE _
&MarketplaceId=ATVPDKIKX0DER&SellerId=A1IMEXAMPLEWRC&SignatureMethod=HmacSHA256&SignatureVersion=2 _
&Timestamp=2012-12-04T22%3A03%3A05Z&Version=2011-10-01

& then the same VBA code 'hashes' the long URL like that above with a private key (that you get from Amazon) to come up with a signed URL....

https://mws.amazonservices.com?&ASINList.ASIN.1=B002KT3XQM&AWSAccessKeyId=AKIAEXAMPLEFWR4TJ7ZQ _
&Action=GetLowestOfferListingsForASIN&MWSAuthToken=amzn.mws.4ea38b7b-f563-7709-4bae-87aeaEXAMPLE _
&MarketplaceId=ATVPDKIKX0DER&SellerId=A1IMEXAMPLEWRC&SignatureMethod=HmacSHA256&SignatureVersion=2 _
&Timestamp=2012-12-04T22%3A03%3A05Z&Version=2011-10-01&Signature=fJFzEXAMPLEEax1vAdKP7l3lwKQUH1r34lcEXAMPLEA%3D

I was *very* relieved that the hashing bit of the download VBA worked when I came to use it, because when i looked at it...I instantly had to lay down in a corner & adopt the foetal position.

Re the XML parsing....basically 2-3 weeks out of my life spent in a darkened room, with the steepest learning curve of anything I've had to do to date.

The problem with parsing XML, is that there aren't (apparently) many who use VBA to do it (vs. say C# or Java etc.) ...therefore not only did I have new (big) concepts grasp but the example XML parsing syntax out there on the net was all over the shop to my eyes at least! A very stressful 2-3 weeks indeed.

Ahem, while I'm here, for the life of me I can't get the syntax correct for something I'm now doing, all I want to do is embed a variable in an SQL command.

dim Marketplace as string
Marketplace = "UK"
DoCmd.RunSQL "DELETE * FROM Marketplace_GetLowestOfferListingsForASIN"

So how to embed the bolded variable above?

(believe me, i've tried to google it, but my brain is now totally fuzzed...I know it needs extra single or double quotes, but just can't get the permutation correct.

 

[jdraw]

dim Marketplace as string
Marketplace = "UK"
DoCmd.RunSQL "DELETE * FROM " & Marketplace & "_GetLowestOfferListingsForASIN"


I'll try to look at those links but I'm not sure what I'm looking for specifically.

 

[peskywinnets]

Your provided syntax worked a treat - thanks (I was going gaga ...I think I need a break from the screen!)

the link to the VBA is only for reference (& anyone finding this thread via google searh keyword will at least have a download link to get them either started with using Amazon's API Or siging a URL)

 

[jdraw]

Regarding your processing of the Amazon lists.

I have mocked up a sample scenario -- very basic.

Since you mention Price, Feedback and Delivery... I created
Seller table, AmazonList with Date and some imaginary PriceRating, FeedbackRating and DeliveryRatings.

I also created a simple algorithm

SellerRankAmazon =   (30 * feedbackRating) 
                              + (30 * DeliveryRating) 
                              + (40 * PriceRating)

You can adjust the equation and the weighting factors as you see fit.

In my mock up I allowed 8 Sellers (1 of which is you XX_ME_XX).
For simplicity each of the rating areas could take on values 1,2 or 3.
I dummied up some data.

ListID	ListDate	sellerID	feedBackRatingID	PricingRatingID	DeliveryRatingID
100               	3/1/16            	1                 	1                 	1                 	1                 
100               	3/1/16            	2                 	3                 	2                 	3                 
100               	3/1/16            	3                 	1                 	3                 	2                 
100               	3/1/16            	4                 	2                 	1                 	3                 
100               	3/1/16            	5                 	3                 	1                 	2                 
100               	3/1/16            	6                 	1                 	2                 	1                 
100               	3/1/16            	7                 	3                 	2                 	1                 
100               	3/1/16            	8                 	2                 	2                 	2                 
100               	3/1/16            	XX_ME_XX          	2                 	1                 	2

The Amazonlist data was processed and a resulting list of Seller and AmazonRanking which was the sorted by Overall Rankin. So you can see where XX_ME_XX fits with respect to the others.

If there are x% with higher rank than you, or w% with lower rank than you, you could make a decision to do XYZ. It's a little hypothetical, but may give you some ideas.

I didn't mock up your real situation where you can get a list of your info, and a separate list of all seller info.
In my view(and untested) you could record your data in a separate table, then do some sort of union query to put your known info in with the general info. Then run a routine, to do what I did in concept, to show overall rankings where you can see your position with respect to others.

From my mockup this is overall ranking for you and the others

seller	amazonRanking	RankingDate
2                 	260	04/03/2016
3                 	210	04/03/2016
8                 	200	04/03/2016
7                 	200	04/03/2016
5                 	190	04/03/2016
4                 	190	04/03/2016
XX_ME_XX        	160	04/03/2016
6                 	140	04/03/2016
1                 	100	04/03/2016

where you can see where you fit in the list. This is done via a query AmazonMyCurrentRanking

SELECT tblAmazonRankingsWithDate.seller
, tblAmazonRankingsWithDate.amazonRanking
, tblAmazonRankingsWithDate.RankingDate
FROM tblAmazonRankingsWithDate
ORDER BY tblAmazonRankingsWithDate.amazonRanking DESC
 , tblAmazonRankingsWithDate.RankingDate DESC;

I'll attach a database with this mock up later. DONE see zip

 

[peskywinnets]

Ok, I have an embryonic strategy...

First create 3 tables with all the field data types already pre-defined (short text, double, true/false etc) ...these tables are going to be filled with data from the 3 pricing APIs.

ok, let's rock...

1. Request data from amazon (3 APIs) - pass a different prefix to each of the API call functions relating to the Amazon site (therefore DE_ ES_ FR_ IT_ UK_)
2. Delete all data in 3 pre-defined tables (the data is disposable....fresh pricing data brought in each time the APIs are called)
3. Pipe extracted pricing information from the returned Amazon XML into the 3 aforementioned Access tables in the function (using the prefixes passed to the function & ensure all the returned data ends up in the correct table)
4. Now ....run some VBA code on the tables to identify & extract key/important data from the table (e,g, what's the lowest 'Fulfilled by Amazon' seller price, what's the lowest 'Fulfilled by Merchant Price' etc), use this VBA extracted data to self populate some additional fields added to the table
5. Ok, now I have all 3 tables populated with the key pricing data....join all 3 tables together using the product field (which Amazon calls an ASIN) & run a query on them (the algorithm) to decide whether any of my prices should be lower or higher.

...going the way of the above, means all I have to do is pass the prefix (UK_, DE_ etc)to the VBA API call function (which means I don't have a lot of headaches with heaps of duplicated queries)

I've tested the theory & it works well, but now I have some major syntax headaches trying to convert some SQL taken from a query for use in VBA...

For example...

SELECT UK_GetLowestOfferListingsForASIN.ID, UK_GetLowestOfferListingsForASIN.AFNIsLowest, First(UK_GetLowestOfferListingsForASIN.Channel) AS FirstOfChannel, UK_GetLowestOfferListingsForASIN.Position, UK_GetLowestOfferListingsForASIN.TotalPrice, UK_GetLowestOfferListingsForASIN.ASIN
FROM UK_GetLowestOfferListingsForASIN
GROUP BY UK_GetLowestOfferListingsForASIN.ID, UK_GetLowestOfferListingsForASIN.AFNIsLowest, UK_GetLowestOfferListingsForASIN.Position, UK_GetLowestOfferListingsForASIN.TotalPrice, UK_GetLowestOfferListingsForASIN.ASIN
HAVING (((UK_GetLowestOfferListingsForASIN.AFNIsLowest)=False) AND ((First(UK_GetLowestOfferListingsForASIN.Channel))="Amazon"))
ORDER BY UK_GetLowestOfferListingsForASIN.ID;

Every UK_ needs to be replaced by a variable (the prefix passed to the function) .....brainache!!

Just to give you a flavour, this is perhaps the most trickiest table (the other two API tables are simple in comparison), this is the main price position 'league table'...



...some of those empty cells on the right AFNLowestPrice & MFNLowestPrice need fully populating (fyi: AFN = Amazon fulfilled & MFN = Merchant fulfilled)

Edit: JDraw, just seen your last contribution (I must have been typing while you submitted it) ...Wow thanks, let me take a look at it now

 

[peskywinnets]

Wow jdraw, you went above & beyond ...i've downloaded your zip file - what you've done is great ...I shall lean on it heavily with this particular task in hand- a hearty thanks