Updating a table with a Count() of another table

[lartigue64]

I'm working with a table of sales, and I'm trying to come up with a way of populating a second table that I will use to report the statistics. ie, the number of sales by area and type for a specific month, quarter or year.
I'm trying to build a query that will fill the statistics table, but haven't found one that works.
For example I want to see the following in the table_Stats:
area1 timeperiod_x year_y type_t number_z
area1 timeperiod_x year_y type_u number_aa
...

The following query is what i want to do, but run in the query window of Access returns the error "Reserved error (-3025); There is no message for this error.

INSERT INTO table_stats
VALUES ('AREA1','January', 2007,'typeA',
(SELECT Count(ID) FROM table1 WHERE [Area]='AREA1' AND [SalesDate]>=#1/01/2007# AND [SalesDate]<=#1/31/2007# AND [Type]='in'))

thank for your help.

 

[genomon]

INSERT INTO needs to be followed by a field list; the VALUES clause provides the actual values.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[lartigue64]

ok, i added the field list, so now my query looks like this:

INSERT INTO table_stats (Area, TimePeriod, Year, Type, Number)
VALUES ('AREA1','January', 2007,'typeA',
(SELECT Count(ID) FROM table1 WHERE [Area]='AREA1' AND [SalesDate]>=#1/01/2007# AND [SalesDate]<=#1/31/2007# AND [Type]='in'))

And now i get a syntax error. "Number" is highlighted.

 

[SgtLoehr]

I forget what they call it... but I think 'Number' is one of those words you can't use. "Reserved word" or something like that. Just try to change the field name and see if it works after that.

Sgt Loehr

 

[lartigue64]

I thought of that also, and changed it, and am back to the original error.
I've thought of breaking this up into two seperate queries, one where i insert area, timeperiod, year, type into the table and then one where i would update the row inserted with the count. but i haven't been able to do this either...

 

[SgtLoehr]

Check your delimiter on your number field.
Delimiter for text = '
Delimiter for date = #
but numbers don't use delimiters

 

[lartigue64]

delimiters are correct according the the field type.
i'm really at a loss here! how frustrating!

 

[SgtLoehr]

Sounds weird... I would try to simplify your SQL to see where the problem is.

1) Check all your field names make sure they aren't reserved words. You can do this by searching them in your object browser, from your example code I would imagine you would have problems with 'Year', 'Type', and 'Number'.
2) That whole (SELECT Count(ID) FROM table1 WHERE [Area]='AREA1' AND [SalesDate]>=#1/01/2007# AND [SalesDate]<=#1/31/2007# AND [Type]='in') might have an error in it as well, so try to simplify this to see if the error is there. Check your datatable and look for a recordset you want to be retrieved, and change the SELECT statement to the value in your recordset. If it works without error, then your error is within that SELECT statement.

I'm no professional, I just started writing SQL a few months ago, but I don't like SELECT statements in an INSERT or UPDATE statement. I'd rather use a dlookup or in your case dcount.

Interesting problem though.

SgtLoehr

 

[lartigue64]

The select part works fine outside of the insert statement. As I changed the field names here for simplification, its not a problem with the field names being reserved words.
Maybe you could help me with a DCount or Dlookup, i've been looking at these, but not quiet sure how they would work in my situation?

 

[MajP]

These are just calculated fields so you do not have to save to a table (unless performance dictates). You could build a function and call the function from a query.

Public Function salesByAreaPeriodType(strArea As String, dtmStart As Date, dtmEnd As Date, strType As String) As Integer
  Dim strWhere As String
  strWhere = "[Area] = '" & strArea & "' AND SalesDate >= # " & dtmStart & "# AND [SalesDate] <= #" & dtmEnd & "# AND [Type] = '" & strType & "'"
  'for error checking uncode next line
  'debug.print strWhere
  salesByAreaPeriodType = DCount("ID", "table1", strWhere)
End Function

Using this function from a query you can pass literals or field names.

 

[MajP]

PS the above code is untested.

 

[SgtLoehr]

try this:

dcount("[ID]","table1","[Area] = 'AREA1' And [SalesDate] >= #1/01/2007# AND [SalesDate] <= #1/31/2007# And [Type] = 'in')

of course you'll have to replace the values with your fields names if the parameters are from a form.

dcount("[ID]","table1","[Area] = '" & textbox & "' AND [SalesDate] >= #" & textbox & "# AND [SalesDate] <= #" & textbox & "# AND [Type] = '" & textbox & "'")

Hope it helps...

SgtLoehr

 

[lespaul]

if you're doing this in code, run one query that gets the count and assign that to a variable and then use the variable in your INSERT query.

run query:
[tt]
SELECT Count(ID) As Counter FROM table1 WHERE [Area]='AREA1' AND [SalesDate]>=#1/01/2007# AND [SalesDate]<=#1/31/2007# AND [Type]='in'[/tt]

assign value to a variable and run your next query:
[tt]INSERT INTO table_stats (Area, TimePeriod, Year, Type, Number)
VALUES ('AREA1','January', 2007,'typeA', variable)[/tt]


Leslie

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

http://www.ubuntu.com

 

[lartigue64]

ok, i found the solution.
I used a DCount to get the count and then put the variable into my insert statement.

thanks everyone for your help.

 

[PHV]

Why not simply this ?
INSERT INTO table_stats
SELECT [Area],Format([SalesDate],'mmmm'),Year([SalesDate]),'typeA',Count(ID)
FROM table1
WHERE [Type]='in'
GROUP BY [Area],Format([SalesDate],'mmmm'),Year([SalesDate])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886