Slickest way to duplicate queries with 'replaced' SQL content

[peskywinnets]

I have lots of queries that need duplicating & then the SQL query data 'searched & replace' to change some prefixes.

It's quite unwieldly, for example steps involved.

1. Copy the 'master query' in Access
2. rename the duplicated query
3. Open the query in Design view (SQL view)
4. Highlight/Copy all the text.
5. Paste the text into Notepad (does access have search replace for SQL text?!!)
6. In Notepad do a 'replace text' (for example replace all occurences of DE_ with ES_
7. Copy revised text.
8. Paste it back into the query.
9. Save the query.

....& that was for just one query (I have lots of them...& on an ongoing basis to!).....there must be a slicker way?!!!

Top tips warmly received.

 

[sneuberg]

I'd like to see the SQL of your master query and tell us what gets changed in it.

You might be able to automate this using querydefs. I believe you can open you master query, pull the SQL out of it into a variable, do your string replacements and then create the new query.

 

[MarkK]

Consider this function . . .

Function ReplaceTags(ByVal SourceText as string, ParamArray Values()) As cString
    Dim i As Integer
    For i = 0 To UBound(Values)
        SourceText = VBA.Replace$(SourceText, "{" & i & "}", Values(i))
    Next
    Set ReplaceTags = SourceText
End Function

Then, consider this SQL . . .

SELECT * FROM Table 
WHERE NumericField = {0} AND DateField = #{1}# AND TextField = '{2}'

. . . which we can expose inside a module as . . .

Private Const SQL_SELECT As String = 
   "SELECT * FROM Table " & _
   "WHERE NumericField = {0} " & _
      "AND DateField = #{1}# " & _
      "AND TextField = '{2}'"

With that said, we can now easily replace values in that SQL with code like . . .

Function GetRecordset(n1 as long, d1 as date, t1 as string) As DAO.Recordset
    dim sql as string
    sql = ReplaceTags(SQL_SELECT, n1, d1, t1)
    Set GetRecordset = CurrentDb.OpenRecordset(sql)
End Function

See what's going on there? And the string replacements don't need to be in the WHERE clause criteria, replace any string you want, so . . .

Private Const SQL_SELECT As String = +
   "SELECT Field{0}, Field(1}, Field{2} " & _
   "FROM Table"

Function GetSQL(Field1 as integer, Field2 as integer, Field3 as integer) as String
   GetSQL = ReplaceTags(Field1, Field2, Field3)
End Function

And then call that like . . .

   dim sql as string
   sql = GetSQL(3, 2, 1) [COLOR="Green"]'returns SQL with "Field3, Field2, Field1" as the field list[/COLOR]

Does that make sense? This might not be that useful if you have a series of saved queries, since the SQL parser will choke on {0}, but I use this approach to string replacements all over the place.
Hope it helps,

 

[AccessBlaster]

Many people use V-TOOLS for searching and replacing within databases.

 

[sneuberg]

This is what I was thinking

Public Sub AddQuery()

Dim MasterQueryDef As DAO.QueryDef
Dim strSQL As String
Dim NewName As String

NewName = "qryNewName"

Set MasterQueryDef = CurrentDb.QueryDefs("qryMaster")
'strSQL has the SQL of the qryMaster
strSQL = MasterQueryDef.SQL
'do replacements like this
strSQL = Replace(strSQL, "TestTable", "PestTable")
'create the new query
CurrentDb.CreateQueryDef NewName, strSQL


End Sub

 

[peskywinnets]

Here's some typical SQL content...

SELECT UK_Pricing.SKU, UK_Pricing.ASIN, UK_Pricing.MyBasePrice AS MyBasePrice, UK_Pricing.MyShippingPrice AS MyShippingPrice, UK_Pricing.MyTotalPrice AS MyTotalPrice, UK_Pricing.Myposition, UK_Pricing.LowestPrice, UK_Pricing.IsLowestAFN, UK_Pricing.LowestAFN, UK_Pricing.[Lowest MFN], UK_Pricing.UndercutBy, UK_Pricing.MyNewBasePriceToMatch, UK_Pricing.DontCompete, UK_Pricing.MyPriceLowest, UK_Pricing.NoOfSellers, UK_Pricing.UK_SellersURL, UK_Pricing.UK_EditURL, UK_Pricing.UK_ProductURL, UK_Pricing.Comments
FROM UK_Pricing
WHERE (((UK_Pricing.UndercutBy)>0));

all that changes in the SQL text is the prefix UK_ changes to be DE_ or FR_ or ES_ or IT_

(i.e. to duplicate/change the master UK query to be a specific query for different countries)

 

[MarkK]

My observation of that is the country code should be in a field IN the table, not in the name of the table. That is a normalization problem that should be remedied via better table design.

 

[CJ_London]

Doesn't look like your data is normalised - why not just have one table for pricing with UK/DE etc as an extra field - then you only need the one query

 

[peskywinnets]

I've only given you all part of the overall picture.

the tables are created by pulling in data from Amazon APIs (that I've whipped up in VBA with a lot of pilfering & kludging of others' code snippets).

Access requests data from Amazon via a 'get' command - then I parse the returned XML data in Access data to end up with a structured CSV file that 'Access friendly'..

I initially imported this CSV data into Access as linked tables, but this caused all manner of problems with read only queries that I revisited & imported the text files as tables in their own right.

So, the data from one of their API calls ends in one table, the result from another API goes into another table, another API ends up in another table...etc.

then this is replicated for Amazon.de Amazon.es Amazon.fr & Amazon.it

The simplest way (for me at least) is rather than thinking of normalization (these are smallish tables), is to simply have them as standalone tables & then create replicated queries for each of the countries.

Besides (& I'm not a database expert), there isn't that much commonality/duplication of data (which is normally the case for normalization?)

Doesn't look like your data is normalised - why not just have one table for pricing with UK/DE etc as an extra field - then you only need the one query

I's not a simple price table (e.g. product A - £13.99 etc), but contains all manner of data (for example the price that every seller for all the 150 products I sell is selling, so I can see where I am in the league table re the competiton's prices etc. IMHO, to put all the returned data into one table would make it excessively 'busy' (& to my noobescent mindset - complicated/cumbersome!)

Many people use V-TOOLS for searching and replacing within databases.

this looks very interesting - thanks for sharing

 

[Galaxiom]

I's not a simple price table (e.g. product A - £13.99 etc), but contains all manner of data (for example the price that every seller for all the 150 products I sell is selling, so I can see where I am in the league table re the competiton's prices etc. IMHO, to put all the returned data into one table would make it excessively 'busy' (& to my noobescent mindset - complicated/cumbersome!)

Definitely a normalization issue.

You are right that it shouldn't be in one table, but you are separating them the wrong way. The price at all sellers (including your own) should be in a related table with one record for each seller and product. That table will also have a CurrencyID field.

Apply the same thinking to the other information.

However you import the data doesn't matter but your next step should be to normalize. Personally I would have the parsing code writing the data from the xml into the normalized structure.

 

[peskywinnets]

The price at all sellers (including your own) should be in a related table with one record for each seller and product. That table will also have a CurrencyID field.
.

Sorry for the n00b line of questioning. Each seller has no ID/name etc. It just looks like this...

ProductA 'Seller Price Position 1' £6.99
ProductA 'Seller Price Position 2' £7.99
ProductA 'Seller Price Position 3' £8.99

if I then run the same API later in the day, it could have all changed...

ProductA 'Seller Price Position 1' £6.79
ProductA 'Seller Price Position 2' £7.89
ProductA 'Seller Price Position 3' £8.99

I have no idea who the sellers are, this is simply pricing 'league table' info.

Does this mean I should still be looking at normalzing?

re parsing the data straight into a tab;e ...yes, I hear you! The reason I dumped the parsed data into to a text file & then imported it, is simply because I don't yet know how write the parsed XML directly into a table! (it's creeping up my todo list rapidly though!)

 

[Galaxiom]

Sorry for the n00b line of questioning. Each seller has no ID/name etc. It just looks like this...

ProductA 'Seller Price Position 1' £6.99
ProductA 'Seller Price Position 2' £7.99
ProductA 'Seller Price Position 3' £8.99

if I then run the same API later in the day, it could have all changed...

ProductA 'Seller Price Position 1' £6.79
ProductA 'Seller Price Position 2' £7.89
ProductA 'Seller Price Position 3' £8.99

I have no idea who the sellers are, this is simply pricing 'league table' info.

Does this mean I should still be looking at normalzing?

Yes if you want to query the price summaries such as Count, Max, Min, Avg etc and how your's compares. A simple aggregate query would return all this information for every product. Imagine the complexity doing this with the data spread out across columns.

The seller position information is inconsequential so I wouldn't even import it. In fact importing it is a breach of normalization since the position can be derived from the price.

 

[peskywinnets]

The seller position information is inconsequential so I wouldn't even import it. In fact importing it is a breach of normalization since the position can be derived from the price.

The thread has morphed somewhat & now taking a different route, hence you've only been getting morsels, so just to expand it a bit, this is more akin to me extracted XML data, which ends up in a table like this....

ProductName   PricePosition      SellerPrice     SellerFeedbackScore         'Fulfilled by Amazon'

   A                1               7.99                  89                         True
   A                2               7.29                  99                         False
   A                3               7.69                  98                         True
   B                1               9.99                  92                         False
   B                2              10.29                  80                         False
   B                3              12.39                  98                         True

The data above is generic 'league table' pricing information...my price will be in there somewhere - but the only way I can establish my price position is to link/join to another table (which contains my actual selling price). By linking the two tables on Sellerprice and ProductName, I can find out my price position & establish if I'm not the lowest price.

Therefore when you say the seller price position is inconsequential, I'm not sure that's true here. Because I really need to know where my price stands in the league table.

Also to decide whether I need to compete on price, I also need to look at the data in other fields such as whether my competitor's seller feedback sucks or whether they are shipping themselves or having Amazon ship for them etc.

Normalization is something I've never thought much about ....nor deployed, but I'm curious that everyone leapt on me for not normalizing bearing in mind the above data structure, how can the data above be normalised?

 

[Grumm]

Well you can link the data you get from Amazon to that table which contains your price right ?

What link is that ? If you know that link then you know that there are unique values in one of the tables. If you have that it is easy to remove duplicate data into an other table.
Normalization is done step by step.

So it is possible that you will have to save the data from that xml into different tables. It will also allow you to have 1 sql and don't need to change prefix or query's.

 

[peskywinnets]

Well you can link the data you get from Amazon to that table which contains your price right ?

Actually all the data comes from Amazon, so I have that table in my last post, plus another table that contains my price (also sourced from Amazon).

What link is that ? If you know that link then you know that there are unique values in one of the tables. If you have that it is easy to remove duplicate data into an other table.

In this situation the tables would be linked on price & ProductName (to get my price position)

What link is that ? If you know that link then you know that there are unique values in one of the tables. If you have that it is easy to remove duplicate data into an other table.

But I'm not sure there is any duplicate data (but then again, I fear I'm not grasping the point!)

 

[Grumm]

So you kind of guess and hope that the line of that article with that price is actually yours ?
What if i sell the same article at the same price ? You wouldn't know if it is yours or not ?

 

[jdraw]

Based on a quick review of this thread, it seems that giving info in bits and pieces has not been conducive to a quick, focused response.
It seems you can request from Amazon a current product/price list. This list shows "current prices" for identified products by "sellers" in a sequential manner based on Price.
And you have a separate list of your current price for (some of) those products.

And it seems the issue is, you want to know your Price position within the range of prices for a given product. And using some algorithm, modify your price based on the latest Product price information from Amazon.

Please adjust as necessary if this is not representing your requirement.

 

[peskywinnets]

So you kind of guess and hope that the line of that article with that price is actually yours ?
What if i sell the same article at the same price ? You wouldn't know if it is yours or not ?

Yes, but I'm not guessing as in 'a wild guess'

In your scenario, I indeed could be out by one postion 1 (or more if there are lots of sellers who are all selling at the same price), but actually what's the main driver here is that my price is competitive (not my exact league table position). Incidentally, in the scenario that two sellers have exactly the same price, Amazon give the 'buy box' 50% of the time to each seller.

There's no way around this 'guessing' because amazon don't return the seller name/ID in their returned data for the 'league table' of pricing.

You may well ask if my exact 'league table' price position isn't critically important, then why try to evn glean it? Well it becomes important if my price is so uncompetitive that my price tucked 3 pages back on the prices page! So it's handy information to have for my reference.

In summary...

One of Amazon API's fetches back all my prices for the products that I sell .....this data all ends up in one Access table.

Another of Amazon's API's retrieves *everybody's* prices (of which mine is contained therein) & this data ends up in another access table.

Since I definitely know my own selling price & since have the pricing 'league table'...I can therefore link the two table on price/product & (approximately) glean where my position is in the league table.

Based on a quick review of this thread, it seems that giving info in bits and pieces has not been conducive to a quick, focused response.
It seems you can request from Amazon a current product/price list. This list shows "current prices" for identified products by "sellers" in a sequential manner based on Price.
And you have a separate list of your current price for (some of) those products.

And it seems the issue is, you want to know your Price position within the range of prices for a given product. And using some algorithm, modify your price based on the latest Product price information from Amazon.

Please adjust as necessary if this is not representing your requirement.

You've completely got it.

The reason for the thread is that I don't just sell on Amazon.co.uk...but also Amazon.de Amazon.fr Amazon.es & Amazon.it & becuase I'm not that savvy with Access (& becuase there's actually not much overlap of data between all the different Amazon sitesa), I've treated them all as separate entities...hence looking for a slicker way of replicating queries!

If I'm candid, having put in an inordinate amoutn of time workinng out how to request the data & parse the returned XML (which from a standing start was a headtrip), I'm now like a 'hare in the headlights' with all this data I now have & the best approach & how to massage the data!


P.S. Yay....I've now sussed how to pipe the data from the returned XML straight into an access tables (vs. parsing XML to text files then importing into access )

 

[jdraw]

Good stuff.
Does your data from Amazon reflect all of your current sales locations?? That is, your price per product per UK or DE or FR etc.
Is there any way you can post what you have, I'll check and see if anything jumps out.
You can PM if you like.

 

[peskywinnets]

Good stuff.
Does your data from Amazon reflect all of your current sales locations?? That is, your price per product per UK or DE or FR etc.

Yes (well ultimately!)

So I'll have about three 'price-centric' tables per 'Amazon site' (five Amazon sites in total)

Table 1 - my prices for the products I sell
Table 2 - everybody's prices for the same products (including mine embedded in there - but without any 100% way of identifying my exact position) & whether these prices are for seller 'self fulfill' or Amazon to fulfill
Table 3 - other pricing info relating to the same products (such as which 'price' is winning the 'buy box' & whether my price is currently lowest)

(the above is not of my choosing but aligned with the 3 related Amazon API's for retrieving pricing info)

So I have to come up with a way of crunching all this data & some type of algorithm to decide whether to move prices up or down. Not as simple as it might sound, because for example ...if someone is lower in price but has poor seller feedback rating & takes 2-3 weeks to deliver, then why on earth would I feel the need to match his price? etc...(so, quite a baptism of fire for me pressing Access into play).

Re your offer of casting your eye over ....that's very kind, but I'd rather keep all of this private ((I really hope you can understand this - it's not meant to be rude), besides I'm in a state of complete flux at the moment (as I say I've just worked out how to pipe the data direct into tables, so having to rejig all my VBA code to embrace this new way of doing things)