SQL Syntax for multiple table joins

[scrawny]

Hi!

I'm trying to code some SQL in the background that can take a grand
total of weight and place it into an order header table.

The tables are mapped out accordingly:

ORDER_HEADER
linked 1 to many on the Order ID to...
ORDER_DETAILS
linked many to 1 on the Product ID to...
MATERIALS

Order details contains length and quantity whilst materials contains
weight/metre. And the field I want to update is in ORDER_HEADER which
is total_weight.

I have tried the following without success:

Dim SQL_Weight As String

SQL_Weight = "UPDATE [ORDER_HEADER] INNER JOIN ([ORDER_DETAIL] INNER
JOIN [MATERIALS] ON [ORDER_DETAIL].[Product ID] = [MATERIALS].[ID]) ON
[ORDER_HEADER].[ID] = [ORDER_DETAIL].[Order_ID] " & _
"SET [ORDER_HEADER].Total_Weight =
SUM([ORDER_DETAIL].Length * [ORDER_DETAIL].Quantity *
[MATERIALS].Weight) WHERE [ORDER_HEADER].[ID] = " &
OrderForm.Txt_OrderID.Value & ";"

DoCmd.RunSQL SQL_Weight

 

[John W. Vinson]

Hi!

I'm trying to code some SQL in the background that can take a grand
total of weight and place it into an order header table.

The tables are mapped out accordingly:

ORDER_HEADER
linked 1 to many on the Order ID to...
ORDER_DETAILS
linked many to 1 on the Product ID to...
MATERIALS

Order details contains length and quantity whilst materials contains
weight/metre. And the field I want to update is in ORDER_HEADER which
is total_weight.

I have tried the following without success:

Dim SQL_Weight As String

SQL_Weight = "UPDATE [ORDER_HEADER] INNER JOIN ([ORDER_DETAIL] INNER
JOIN [MATERIALS] ON [ORDER_DETAIL].[Product ID] = [MATERIALS].[ID]) ON
[ORDER_HEADER].[ID] = [ORDER_DETAIL].[Order_ID] " & _
"SET [ORDER_HEADER].Total_Weight =
SUM([ORDER_DETAIL].Length * [ORDER_DETAIL].Quantity *
[MATERIALS].Weight) WHERE [ORDER_HEADER].[ID] = " &
OrderForm.Txt_OrderID.Value & ";"

DoCmd.RunSQL SQL_Weight

Access doesn't support updating of any Totals query, even one like this which
logically should be updateable.

In general, though, storing derived data such as this in your table
accomplishes three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[scrawny]

That makes sense in a way.
The assumption was going to be that this table could never be modified
without using the form. But this grand total is needed in other
calculations... thus my solution then became below... but I'm
wondering if there is a simpler way? I prefer using SQL rather than
Access' query builder.

Public Function Get_Weight(lOrderID As Long) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL_Weight As String

Set db = CurrentDb
SQL_Weight = "SELECT
SUM([ORDER_DETAILS].Length*[ORDER_DETAILS].Quantity*[MATERIALS].Weight)
FROM [ORDER_DETAILS] INNER JOIN [MATERIALS] ON [ORDER_DETAILS].
[Product ID] = [MATERIALS].ID GROUP BY [ORDER_DETAILS].[Order_ID]
HAVING ([ORDER_DETAILS].[Order_ID] = " & lOrderID & ");"
Set rs = db.OpenRecordset(SQL_Weight)

If Not (rs.BOF And rs.EOF) Then
Get_Weight = rsQ.Fields.Item(0).Value
Else
Get_Weight = 0
End If

rs.Close
db.Close

End Function

 

[John W. Vinson]

That makes sense in a way.
The assumption was going to be that this table could never be modified
without using the form. But this grand total is needed in other
calculations...

So... redo the calculation in the other calculations, unless you have
DEMONSTRATED (not assumed!) inadequate performance.

thus my solution then became below... but I'm
wondering if there is a simpler way? I prefer using SQL rather than
Access' query builder.

The query builder is simply a tool to make it easier to construct SQL; the
query *IS* SQL, no matter how the SQL gets created. By all means construct the
query in SQL if you prefer, it certainly gives you more control and often
gives you a better query.

Public Function Get_Weight(lOrderID As Long) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL_Weight As String

Set db = CurrentDb
SQL_Weight = "SELECT
SUM([ORDER_DETAILS].Length*[ORDER_DETAILS].Quantity*[MATERIALS].Weight)
FROM [ORDER_DETAILS] INNER JOIN [MATERIALS] ON [ORDER_DETAILS].
[Product ID] = [MATERIALS].ID GROUP BY [ORDER_DETAILS].[Order_ID]
HAVING ([ORDER_DETAILS].[Order_ID] = " & lOrderID & ");"
Set rs = db.OpenRecordset(SQL_Weight)

If Not (rs.BOF And rs.EOF) Then
Get_Weight = rsQ.Fields.Item(0).Value
Else
Get_Weight = 0
End If

Erm? Why not just

=DSum("[ORDER_DETAILS].Length*[ORDER_DETAILS].Quantity*[MATERIALS].Weight",
"queryname", "[Order_ID] = " & [Order_ID])

where queryname is a simple select query joining the two tables? You can and
should include the OrderID criterion in queryname's WHERE clause for better
efficiency. At least give it a try.

The DSum() will not break updatability if you decide that you prefer to store
the derived data.

rs.Close
db.Close

End Function

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[scrawny]

Ah! I see now... that might simplify things.

Is that "queryname" a predefined Access Query, or can it be an SQL
string like sql_query = "SELECT * FROM ORDER_DETAILS INNER JOIN..."
blah blah blah.

Such that it becomes:

=DSum("[ORDER_DETAILS].Length*[ORDER_DETAILS].Quantity*[MATERIALS].Weight",
sql_query, "[Order_ID] = " & [Order_ID])

 

[John W. Vinson]

Ah! I see now... that might simplify things.

Is that "queryname" a predefined Access Query, or can it be an SQL
string like sql_query = "SELECT * FROM ORDER_DETAILS INNER JOIN..."
blah blah blah.

It does need to be a stored query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David W. Fenton]

m:

rs.Close
db.Close

You need this:

Set rs = Nothing

....after the rs.Close, and you should replace:

db.Close

....with:

Set db = Nothing

You can't close CurrentDB, and that's what you initialized your
variable with, but in both cases, you need to make sure the pointers
are destroyed, since it's possible for the memory to remain
unreleased if VBA loses its mind and forgets that there are no
longer any references pointing to those memory structures.

 

[David W. Fenton]

It does need to be a stored query.

....well, a stored query or a table name.