Update query question

[maheumann via AccessMonster.com]

Good morning,

I have a quick question, I have this database that has all of our tracking
information for about 30 locations based on 10 procedures. We then look at
each procedure for each PDC and calculate the minutes it took each procedure
for each PDC and find through that a Standard Deviation. Now the locations
are hoping to lower that ST "standard deviation" by tackling their highest
problem areas.

I've been asked to go in and for each location show the top 500 worst times
for each procedure. Now the update query to do that is created, my problem
is I need to change the update query’s Location number and procedure about
300 times to get all the locations 500 worst procedures.

I tried creating a macro where I used the update query for one location and
then I thought to set the value of the update query "that is the drop down
set value in the macros tab" to another number, well I couldn’t find my
update query and it bombed out afterwards.

So my question is how to I automate this to go through the 300 different
configurations where I don't have to build 300 update queries.

Oh and the output will be in one sheet where one location will have all 10
procedures shown in groups of each individual procedure from top to bottom,
then saved as a PDF file.


Thanks for your help, apologies if I didn't explain it enough.

Thanks

MH

 

[Klatuu]

It would be helpful it you post the SQL for the query you are asking about.

 

[maheumann via AccessMonster.com]

It would be helpful it you post the SQL for the query you are asking about.

Good morning,

[quoted text clipped - 27 lines]

MH

For the SQL I'm only going into the macros section of Access and trying to
update it through there.

There is no true VBA built other than what the computers macro tab in Access
has already had built into it.

Sorry does that help?

MH

 

[Klatuu]

You said you created an Update query. I need the SQL for that query.
To get it, open the query in design view.
In the upper lieft corner of the screen will be a small button with the
design icon and a drop down arrow on it's right hand side.
Drop that button down and select SQL.
You design view will change to show the text of the query.
Copy that text and paste it into a reply.
That is what I need to see.
--
Dave Hargis, Microsoft Access MVP

It would be helpful it you post the SQL for the query you are asking about.

Good morning,

[quoted text clipped - 27 lines]

MH

For the SQL I'm only going into the macros section of Access and trying to
update it through there.

There is no true VBA built other than what the computers macro tab in Access
has already had built into it.

Sorry does that help?

MH

 

[maheumann via AccessMonster.com]

You said you created an Update query. I need the SQL for that query.
To get it, open the query in design view.
In the upper lieft corner of the screen will be a small button with the
design icon and a drop down arrow on it's right hand side.
Drop that button down and select SQL.
You design view will change to show the text of the query.
Copy that text and paste it into a reply.
That is what I need to see.[quoted text clipped - 12 lines]

MH

Apologies of course.

INSERT INTO 3129 ( [Event #], [Event Description], [Rec PDC], [Calc Hours],
[Pick Ticket], Part, [Type of Ticket], ShipPDC, StDtTime, EndDtTime,
[Reciveing PDC Name] )
SELECT TOP 500 SigmaData.EventCode, "In-Yard to Stock Kept" AS [Event
Description], SigmaData.RecvPDC, [CalcMins]/60 AS CalcHours, SigmaData.
PickTicket, SigmaData.Part, IIf([TicketType]="B","Replenishment","Freight
Forward") AS [Type of Ticket], SigmaData.ShipPDC, SigmaData.StartDtTm,
SigmaData.EndDtTm, [PDC List_1].[PDC Name] AS RecvPDCName
FROM (SigmaData LEFT JOIN [PDC List] ON SigmaData.ShipPDC = [PDC List].[PDC
Code Short]) LEFT JOIN [PDC List] AS [PDC List_1] ON SigmaData.RecvPDC = [PDC
List_1].[PDC Code Short]
GROUP BY SigmaData.EventCode, "In-Yard to Stock Kept", SigmaData.RecvPDC,
[CalcMins]/60, SigmaData.PickTicket, SigmaData.Part, IIf([TicketType]="B",
"Replenishment","Freight Forward"), SigmaData.ShipPDC, SigmaData.StartDtTm,
SigmaData.EndDtTm, [PDC List_1].[PDC Name], [PDC List].[PDC Name]
HAVING (((SigmaData.EventCode)=1) AND ((SigmaData.RecvPDC)=3129))
ORDER BY SigmaData.RecvPDC, [CalcMins]/60 DESC;

Sorry about it being messy I'm not really sure as how to wrap the test around
for those who read code to better understand, if that makes sense.

mh

 

[Klatuu]

I am assuming 3129 is the name of the table you want to append records to. I
think the easiest way to do this will be to either use or create a table that
has a list of all the file names you want to append the records to. Then you
will need to use some recordset processing and some dynamic SQL to do this.

For starters, the way to wrap code in VBA is to use the line continuation
character. It is the Underscorce _ chararcter. It has to be preceeded by a
space. To create one string on two lines, you have to have the quotes around
all parts of the string. For example, on one line:

strStuff = "This is one long line of text to show how to do a line break"

And to put it on two line:

strStuff = "This is one long line of text to " & _
"show how to do a line break"

You will not be using a stored query, but dynamic SQL. You can use the
Replace function to put the file name into the SQL string you will execute.
Start by creating a constant with the SQL string and a dummy value so you
will know where to put the file name:

Dim conStartString As String = "INSERT INTO GozHere ( [Event #], " & _
"[Event Description], [Rec PDC], [Calc Hours], [Pick Ticket], Part, " & _
"[Type of Ticket], ShipPDC, StDtTime, EndDtTime, [Reciveing PDC Name]) "
& _
"SELECT TOP 500 SigmaData.EventCode, ""In-Yard to Stock Kept""" & _
" AS [Event Description], SigmaData.RecvPDC, [CalcMins]/60 " & _
"AS CalcHours, SigmaData.PickTicket, SigmaData.Part, " & _
"IIf([TicketType]=""B"",""Replenishment"",""Freight Forward"") " & _
"AS [Type of Ticket], SigmaData.ShipPDC, SigmaData.StartDtTm, " & _
"SigmaData.EndDtTm, [PDC List_1].[PDC Name] AS RecvPDCName " & _
"FROM (SigmaData LEFT JOIN [PDC List] ON SigmaData.ShipPDC = " & _
"[PDC List].[PDC Code Short]) LEFT JOIN [PDC List] AS [PDC List_1] " & _
"ON SigmaData.RecvPDC = [PDC List_1].[PDC Code Short] " & _
"GROUP BY SigmaData.EventCode, ""In-Yard to Stock Kept"", " & _
"SigmaData.RecvPDC, [CalcMins]/60, SigmaData.PickTicket, " & _
"SigmaData.Part, IIf([TicketType]=""B"", ""Replenishment"", " & _
"""Freight Forward""), SigmaData.ShipPDC, SigmaData.StartDtTm, " & _
"SigmaData.EndDtTm, [PDC List_1].[PDC Name], [PDC List].[PDC Name] " & _
"HAVING SigmaData.EventCode = 1 AND SigmaData.RecvPDC = " & _
"ORDER BY SigmaData.RecvPDC, [CalcMins]/60 DESC;"

'SQL String to Execute
Dim strSQL As String

Dim dbf As Database
Dim rst As Recordset

Set dbf = Currentdb
Set rst = dbf.OpenRecorset("tblFileList")
With rst
If .RecordCount = 0 Then
MsgBox "No Files To Process"
rst.Close
Set rst = Nothing
Exit Sub
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strSQL = Replace(conStartString, "GozHere", ![FILE_NAME])
dbf.Execute strSQL, dbFailOnError
.MoveNext
Loop
.Close
Set rst = Nothing
Set dbf = Nothing
End If
End With

Don't be surprised if you get a syntax error. This is just air code written
in the reply box.

--
Dave Hargis, Microsoft Access MVP

You said you created an Update query. I need the SQL for that query.
To get it, open the query in design view.
In the upper lieft corner of the screen will be a small button with the
design icon and a drop down arrow on it's right hand side.
Drop that button down and select SQL.
You design view will change to show the text of the query.
Copy that text and paste it into a reply.
That is what I need to see.

It would be helpful it you post the SQL for the query you are asking about.
Good morning,

[quoted text clipped - 12 lines]

MH

Apologies of course.

INSERT INTO 3129 ( [Event #], [Event Description], [Rec PDC], [Calc Hours],
[Pick Ticket], Part, [Type of Ticket], ShipPDC, StDtTime, EndDtTime,
[Reciveing PDC Name] )
SELECT TOP 500 SigmaData.EventCode, "In-Yard to Stock Kept" AS [Event
Description], SigmaData.RecvPDC, [CalcMins]/60 AS CalcHours, SigmaData.
PickTicket, SigmaData.Part, IIf([TicketType]="B","Replenishment","Freight
Forward") AS [Type of Ticket], SigmaData.ShipPDC, SigmaData.StartDtTm,
SigmaData.EndDtTm, [PDC List_1].[PDC Name] AS RecvPDCName
FROM (SigmaData LEFT JOIN [PDC List] ON SigmaData.ShipPDC = [PDC List].[PDC
Code Short]) LEFT JOIN [PDC List] AS [PDC List_1] ON SigmaData.RecvPDC = [PDC
List_1].[PDC Code Short]
GROUP BY SigmaData.EventCode, "In-Yard to Stock Kept", SigmaData.RecvPDC,
[CalcMins]/60, SigmaData.PickTicket, SigmaData.Part, IIf([TicketType]="B",
"Replenishment","Freight Forward"), SigmaData.ShipPDC, SigmaData.StartDtTm,
SigmaData.EndDtTm, [PDC List_1].[PDC Name], [PDC List].[PDC Name]
HAVING (((SigmaData.EventCode)=1) AND ((SigmaData.RecvPDC)=3129))
ORDER BY SigmaData.RecvPDC, [CalcMins]/60 DESC;

Sorry about it being messy I'm not really sure as how to wrap the test around
for those who read code to better understand, if that makes sense.

mh

 

[maheumann via AccessMonster.com]

Thank you so much,

You've given me a lot to think about.

I'm going to try this out and I'll repost when I have had some time to work
it through.

MH

I am assuming 3129 is the name of the table you want to append records to. I
think the easiest way to do this will be to either use or create a table that
has a list of all the file names you want to append the records to. Then you
will need to use some recordset processing and some dynamic SQL to do this.

For starters, the way to wrap code in VBA is to use the line continuation
character. It is the Underscorce _ chararcter. It has to be preceeded by a
space. To create one string on two lines, you have to have the quotes around
all parts of the string. For example, on one line:

strStuff = "This is one long line of text to show how to do a line break"

And to put it on two line:

strStuff = "This is one long line of text to " & _
"show how to do a line break"

You will not be using a stored query, but dynamic SQL. You can use the
Replace function to put the file name into the SQL string you will execute.
Start by creating a constant with the SQL string and a dummy value so you
will know where to put the file name:

Dim conStartString As String = "INSERT INTO GozHere ( [Event #], " & _
"[Event Description], [Rec PDC], [Calc Hours], [Pick Ticket], Part, " & _
"[Type of Ticket], ShipPDC, StDtTime, EndDtTime, [Reciveing PDC Name]) "
& _
"SELECT TOP 500 SigmaData.EventCode, ""In-Yard to Stock Kept""" & _
" AS [Event Description], SigmaData.RecvPDC, [CalcMins]/60 " & _
"AS CalcHours, SigmaData.PickTicket, SigmaData.Part, " & _
"IIf([TicketType]=""B"",""Replenishment"",""Freight Forward"") " & _
"AS [Type of Ticket], SigmaData.ShipPDC, SigmaData.StartDtTm, " & _
"SigmaData.EndDtTm, [PDC List_1].[PDC Name] AS RecvPDCName " & _
"FROM (SigmaData LEFT JOIN [PDC List] ON SigmaData.ShipPDC = " & _
"[PDC List].[PDC Code Short]) LEFT JOIN [PDC List] AS [PDC List_1] " & _
"ON SigmaData.RecvPDC = [PDC List_1].[PDC Code Short] " & _
"GROUP BY SigmaData.EventCode, ""In-Yard to Stock Kept"", " & _
"SigmaData.RecvPDC, [CalcMins]/60, SigmaData.PickTicket, " & _
"SigmaData.Part, IIf([TicketType]=""B"", ""Replenishment"", " & _
"""Freight Forward""), SigmaData.ShipPDC, SigmaData.StartDtTm, " & _
"SigmaData.EndDtTm, [PDC List_1].[PDC Name], [PDC List].[PDC Name] " & _
"HAVING SigmaData.EventCode = 1 AND SigmaData.RecvPDC = " & _
"ORDER BY SigmaData.RecvPDC, [CalcMins]/60 DESC;"

'SQL String to Execute
Dim strSQL As String

Dim dbf As Database
Dim rst As Recordset

Set dbf = Currentdb
Set rst = dbf.OpenRecorset("tblFileList")
With rst
If .RecordCount = 0 Then
MsgBox "No Files To Process"
rst.Close
Set rst = Nothing
Exit Sub
Else
.MoveLast
.MoveFirst
Do While Not .EOF
strSQL = Replace(conStartString, "GozHere", ![FILE_NAME])
dbf.Execute strSQL, dbFailOnError
.MoveNext
Loop
.Close
Set rst = Nothing
Set dbf = Nothing
End If
End With

Don't be surprised if you get a syntax error. This is just air code written
in the reply box.
[quoted text clipped - 34 lines]

mh