Query must contain at least one table or query 1

[Tadynn]

Hi all,

Access 2K

I'm having problems with a piece of code that I wrote to create two tables and append data to those tables once created.

The problem appears to be when the code runs to steps 3 and 4 (see below) step 3 makes a table and inserts data and step 4 appends more data to the newly created table.

Can anyone suggest anything to fix it?


Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

DoCmd.SetWarnings False

'STEP 1: Create Lnk_AreaPicks table from tbl_Areapicks table
DoCmd.RunSQL "SELECT tbl_AreaPicks.PDate, tbl_AreaPicks.ModuleLow, " & _
"tbl_AreaPicks.ModuleHigh, tbl_AreaPicks.ModuleTotal, tbl_AreaPicks.ShelvingT, " & _
"tbl_AreaPicks.RackLow, tbl_AreaPicks.RackHigh, tbl_AreaPicks.Bulk, " & _
"tbl_AreaPicks.OtherT, tbl_AreaPicks.RackTotal, tbl_AreaPicks.TotalHits " & _
"INTO Lnk_AreaPicks " & _
"FROM tbl_AreaPicks " & _
"WHERE (((tbl_AreaPicks.PDate) Between [Forms]![frm_LinkFilter]![FromDate] " & _
"And [Forms]![frm_LinkFilter]![ToDate]))"

'STEP 2: Create Lnk_AreaPicksCrossTab and append Module Low data
DoCmd.RunSQL "SELECT 'Module Low' AS Area, Lnk_AreaPicksTotals.SumOfModuleLow " & _
"AS Total INTO Lnk_AreaPicksCrossTab " & _
"FROM Lnk_AreaPicksTotals"

'STEP 3: Create Lnk_AreaPicksCrossTabTotal table and insert Module totals
DoCmd.RunSQL "SELECT 'Module Total' AS Area, Sum(Lnk_AreaPicks.ModuleTotal)" & _
"AS Total INTO Lnk_AreaPicksCrossTabTotal" & _
"FROM Lnk_AreaPicks " & _
"GROUP BY 'Module Total'"

'STEP 4: Append Module High
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Module High' AS Area, Lnk_AreaPicksTotals.SumOfModuleHigh AS Total" & _
"FROM Lnk_AreaPicksTotals"

'STEP 5: Append Shelving
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Shelving' AS Area, Lnk_AreaPicksTotals.SumOfShelvingT AS Total" & _
"FROM Lnk_AreaPicksTotals"

'STEP 6: Append Rack low
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Rack Low' AS Area, Lnk_AreaPicksTotals.SumOfRackLow AS Total" & _
"FROM Lnk_AreaPicksTotals"

'STEP 7: Append Rack high
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Rack High' AS Area, Lnk_AreaPicksTotals.SumOfRackHigh AS Total" & _
"FROM Lnk_AreaPicksTotals"

'STEP 8: Append Bulk
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Bulk' AS Area, Lnk_AreaPicksTotals.SumOfBulk AS Total" & _
"FROM Lnk_AreaPicksTotals"

'STEP 9: Append Other
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Other' AS Area, Lnk_AreaPicksTotals.SumOfOtherT AS Total" & _
"FROM Lnk_AreaPicksTotals"


'STEP 10: Append Rack Totals
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTabTotal ( Area, Total )" & _
"SELECT 'Rack Total' AS Area, Sum(Lnk_AreaPicks.RackTotal) AS Total" & _
"FROM Lnk_AreaPicks" & _
"GROUP BY 'Rack Total'"

'STEP 11: Append Bulk totals
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTabTotal ( Area, Total )" & _
"SELECT 'Bulk Total' AS Area, Sum(Lnk_AreaPicks.Bulk) AS Total" & _
"FROM Lnk_AreaPicks" & _
"GROUP BY 'Bulk Total'"

'STEP 12: Append Other Totals
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTabTotal ( Area, Total )" & _
"SELECT 'Other Total' AS Area, Sum(Lnk_AreaPicks.OtherT) AS Total" & _
"FROM Lnk_AreaPicks" & _
"GROUP BY 'Other Total'"

DoCmd.SetWarnings True

DoCmd.Close

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub



Thanks in advance, Tadynn

 

[TonyJollans]

Hi Tadynn,

I think you're just missing some spaces separating the words ..

[blue][green]'STEP 3: Create Lnk_AreaPicksCrossTabTotal table and insert Module totals[/green]
DoCmd.RunSQL "SELECT 'Module Total' AS Area, Sum(Lnk_AreaPicks.ModuleTotal)[highlight] [/highlight]" & _
"AS Total INTO Lnk_AreaPicksCrossTabTotal[highlight] [/highlight]" & _
"FROM Lnk_AreaPicks " & _
"GROUP BY 'Module Total'"

[green]'STEP 4: Append Module High[/green]
DoCmd.RunSQL "INSERT INTO Lnk_AreaPicksCrossTab ( Area, Total )" & _
"SELECT 'Module High' AS Area, Lnk_AreaPicksTotals.SumOfModuleHigh AS Total[highlight] [/highlight]" & _
"FROM Lnk_AreaPicksTotals"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

In STEP 3 you are creating the table

[blue]Lnk_AreaPicksCrossTab[/blue][red]Total[/red]

but in STEP 4-9 you are inserting in

[blue]Lnk_AreaPicksCrossTab[/blue]

Should they not be the same?

 

[Tadynn]

TonyJollans/Golom

I removed the spaces and even copied the code into a sql query and all appear to run okay. I even removed all the spaces but still nothing.

Golom, Steps 4 through to 9 even though they appear the same are actually different. The criteria is set by the area name. So step 3 creates the table and inserts the total where the area is Module Total, step 4 where area is Module High, step 5 Module high, and so on.....



Tadynn

 

[TonyJollans]

Hi Tadynn,

I was suggesting you ADDED some spaces, not removed some!

What I did was:

(a) cut and pasted your STEP 3 statement as posted into a module.
(b) changed "DoCmd.RunSQL" to "A ="
(c) stepped the code past the line - so the variable A was set
(d) in the immediate pane, entered [blue]?A[/blue]
(e) cut and pasted the result into a new query SQL window
(f) tried to run it and got error "Query input must contain at least one table"
(g) eyeballed the statement and saw " ... INTO Lnk_AreaPicksCrossTabTotalFROM ..." - clearly a missing space.

So, what exactly is the problem / error you (now) have? Do you get an error message, or do you just not get the results you expect?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Tadynn]

Apologise for taking so long with a reply. Had internet troubles.....

Tony Jollans, that was exactly what it was. After I added a space or two the query worked fine. So thanks for that.

Gold star for you

Rgrds, Tadynn