Conditional Update Query

[TheShabz]

Hi everyone,

I'm having a tough time wrapping my head around this. I have a table that, among other things, consists of account numbers and campaign dates. I have a blank field that needs to be updated with which iteration of the campaign each campaign date refers to for each account number. For example, My final table should look like:

Account, CampaignDate, Iteration
123, 1/1/2011, CampaignType & 1
123, 1/2/2011, CampaignType & 2
123, 1/3/2011, CampaignType & 3
124, 1/3/2011, CampaignType & 1
124. 1/4/2011, CampaignType & 2
125, 1/1/2011, CampaignType & 1
125, 1/2/2011, CampaignType & 2
125, 1/3/2011, CampaignType & 3
125, 1/4/2011, CampaignType & 4



The Iteration field is what will need to be updated (currently blank). The CampaignType in this case will remain constant as I have a table for each campaign (excel imports, not part of a db) The reason for this is that I will join all the campaign tables together and then be creating a crosstab that will provide the campaignType&Iteration (column) and campaign date (values) for each account number (row).

Any help is appreciated.

[aytee111]

Where is the value of the campaign type coming from? How will you know what to update it with? Are you appending records or do all records exist already?

[June7]

You want to update field of existing records to a specific string value?

Use the query designer and build an UPDATE query. Since the value is not coming from another table, just have the one table to be updated in the query window, click UPDATE and in the Update To row under the appropriate field type the string. Run query.

[TheShabz]

aytee111, the value of the campaign is a static string and the iteration.

June, that's the part I'm stuck on. If we look at account 123 in the example, I need the earliest date to be '"String" & 1' and the next date '"String" & 2'. Essentially, I'm looking to do a GROUP BY Account and First(CampaignDate) within the Update query. I can then do the same and add in WHERE CampaignDate Is Null. I can do the grouping in my head, I just cant figure out how to do that in an Update query.

[June7]

Okay, might not be necessary to actually include that sequential value in the update string. That number can be generated whenever desired by this method: http://forums.aspfree.com/microsoft-...ge-440477.html

However, if you must store it, I suppose this could somehow be included with the UPDATE action, never tried.

[TheShabz]

Its not merely ordering them as-is. The dates wont necessarily be in chronological order.
I will have cases of

Acct, Date, Iteration
123, 1/5/2011, CampaignType & 2
123, 1/4/2011, CampaignType & 1

This won't be a case of a faux autonumber. It needs to follow the dates associated with each account number. And, as mentioned this will be used to eventually create a crosstab that will look like:

Acct, Campaign1, Campaign2
123, 1/4/2011, 1/5/2011

[June7]

Here's what I learned.

This query will produce the number sequence.
SELECT x.CampaignDate, x.Account, (SELECT Count(*) +1 FROM Table1 WHERE Account = x.Account AND CampaignDate<x.CampaignDate) AS ColB
FROM Table1 AS x
ORDER BY x.Account, x.CampaignDate;

Unfortunately, trying to use in an UPDATE action get error 'Must be an updatable query'. So UPDATE is not going to work.

This query will create the Iteration data.
SELECT Table1.Account, Table1.CampaignDate, "CampaignType" & [ColB] AS Iteration
FROM Query1 RIGHT JOIN Table1 ON (Query1.CampaignDate = Table1.CampaignDate) AND (Query1.Account = Table1.Account);

I tried using that query as the basis for a CROSSTAB but the Access query engine chokes on the alias x table in the first query. This query does work for a MAKE TABLE action.
SELECT Table1.Account, Table1.CampaignDate, "CampaignType" & [ColB] AS Iteration INTO table2
FROM Query1 RIGHT JOIN Table1 ON (Query1.CampaignDate = Table1.CampaignDate) AND (Query1.Account = Table1.Account);