SQL Update query... grrrr

[Shippwreck]

Hi,

I'm trying to write an update query but its not working as i want it to... i'll explain!

Table1:
ID, Day1, Day2, Day3, Day4, Day5, Day6, Day7

Table2:
Date, ID, Unit, SellingPrice

Table1 contains unique ID's and then price data for the past 7 days. Table2 contains actual dates in the format 22/10/2007 the same unique ID's as Table1 and prices in the form 5.96 I ignore units, they mean nothing at the moment

I get a new Table2 each week covering the last week, so if there were 100 ID's it would have 100 lines for each day, ie 700 lines.

I want to write a query that updates the prices between the two, Day1 being the oldest day in the week. I'm happy to update 1 day at a time and run teh query 7 times in a VBA loop or do all at once. The query i have written below attempts one day at a time:

UPDATE [4_week_prices], [Vol_Price_Input]
SET [4_week_prices].[Day1] = [Vol_Price_Input].[SellingPrice]
WHERE [4_week_prices].[ID]=[Vol_Price_Input].[ID]
AND [Vol_Price_Input].[sellingprice]=#10/22/2007#;

Thanks for any help you can give!

Matt

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[lespaul]

Is it too late to change your table structure?....you've committed spreadsheet. Your first table should be structured more like:

[tt]
ID PriceDate Price[/tt]

then you wouldn't have to worry about any update you could just run a query to get the last seven days information.



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Shippwreck]

Yes it is too late as there are many other files that are dependant on that table structure.

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[lespaul]

that's a real shame....perhaps you'd be better off posting in Forum707 and coming up with a function, I don't think a query will be a practical solution.

For future reference, you may want to refer to the Fundamentals of Relational Database Design

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Shippwreck]

Despite lespaul's advice i was sure that an SQL solution wasn't only possible, but really couldn't be that difficult and would certainly be quicker than working with recordsets and individual records in VBA... and i was right!

For anyone interested, here it is:

UPDATE [4_week_sales] INNER JOIN [Vol_Price_Input] ON [4_week_sales].[TPNB] = [Vol_Price_Input].[tpnb]
SET [4_week_sales].[Day1] = [Vol_Price_Input].[count of order line units]
WHERE ((([Vol_Price_Input].[check out date])=#22/10/2007#));

and the very simple VBA code to loop through it 7 times (once for each day) is:

For i = 1 To 7
Daynum = "Day" & i + 21
MinDate = rs1!Date + i - 1
DoCmd.RunSQL "UPDATE [4_week_sales] INNER JOIN [Vol_Price_Input] ON [4_week_sales].[TPNB] = [Vol_Price_Input].[tpnb] SET [4_week_sales]." & Daynum & " = [Vol_Price_Input].[count of order line units] WHERE ((([Vol_Price_Input].[check out date])=#" & MinDate & "#));"
Next i


Lastly, i did take a brief look at the article mentioned, didn't really tell me anything i wasn't aware of, however this paragraph did strike me...

Breaking the Rules: When to Denormalize
Sometimes it's necessary to break the rules of normalization and create a database that is deliberately less normal than it otherwise could be. You'll usually do this for performance reasons or because the users of the database demand it. While this won't get you any points with database design purists, ultimately you have to deliver a solution that satisfies your users. If you do break the rules, however, and decide to denormalize you database, it's important that you follow these guidelines:

Break the rules deliberately; have a good reason for denormalizing.

Be fully aware of the tradeoffs this decision entails.

Thoroughly document this decision.

Create the necessary application adjustments to avoid anomalies.

Thanks for the help

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[dhookom]

I would create a crosstab query of your Table2.

TRANSFORM First(SellingPrice) AS FirstOfSellingPrice
SELECT ID
FROM ShippwreckTable2
GROUP BY ID
PIVOT "Day" & DCount("*","ShippwreckTable2","ID='" & [ID] & "' and Date <=#" & [Date] & "#");

Depending on how you want your days numbered, you could change the "<=" to ">=".

Use the crosstab as the base of a query to append records to an empty, table1.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

Break the rules deliberately; have a good reason for denormalizing.

In this case there wasn't a good reason for denormalizing.

However, I understand that we often inherit badly implemented solutions. My livelihood depends on them.

 

[Shippwreck]

Well actually the reason for denormalizing was that the customer demanded it...

There is not just a single table in this format, but three, stock, price and units sold... the customer wanted the output to be an Excel report that graphed each line individually over the week... therefore having day of the week along the top and line down the side makes perfect sense. All the processing is performed in Access, with the final result (the three tables) exported to (amongst other things) an Excel template that graphs all the lines.

Please, don't get me wrong i KNOW its bad DB structure in general... but for the purpose... its perfect. And yes... my livelihood also depends on inheriting bad solutions that need fixing.

I think though that this thread now has the potential to degenerate wildly... so i'll say thanks to all the inputs... the question has been solved and i can go off on weekend a happy man knowing this particular issue is fixed.

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[dhookom]

Did you at least consider my solution using a crosstab query to append to your table everyone hates ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Shippwreck]

LOL, I did consider it and i do use the cross tabs when manually working with the data, but I was certain that there was a simpler query so i pursued that route.

thanks though

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me