Search results for query: *

Hello. This is the first part of a longer sql. datesql and combo sql comprise the "where" statement - both are derived from combobox choices. This sql statement worked until I added the "Count([tbIntervention].[loc])/" & _ "(Select Count([tbIntervention].[loc]) " part, and placed...

Given the sql statement: rptSQL = "SELECT [tbIntervention].[loc], " & _ "Count([tbIntervention].[loc]) AS [Number], " & _ "Count([tbIntervention].[loc])/" & _ "(Select Count([tbIntervention].[loc]) " & _ "FROM tbIntervention WHERE " & _ dateSQL & "...

Thanks. You've been very patient. Now I am getting an error "Multi-level Group By clause is not allowed in a subquery." What does this mean? The report only has one group = loc.

Another question please. The query I use ... Select Case rpttype Case 1 rptSQL = "SELECT [tbIntervention].[loc], " & _ "Count([tbIntervention].[loc]) AS [Number], " & _ "Count([tbIntervention].[loc])/" & _ "(Select Count([tbIntervention].[loc]) " & _ "FROM...

Hi. I see I had a parenthesis off ... thank you very much! RT

Didn't do it. So.. My SQL... rptSQL = "SELECT Distinct [tbIntervention].[loc], " & _ "Count([tbIntervention].[loc]) as [Number], " & _ "Count(tbIntervention.[loc])/" & _ "(Select Count([tbIntervention].[loc]) " & _ "FROM tbIntervention AS [percent]) " & _...

The error message reads "Can't have aggregate function in expression (Sum((Count([number])/(Sum(Count([number])))).

Hi. Thanks for the response, but that was my first try. Doesn't work because of the aggregate sum. Access help says ... "You can't use the name of a control in an expression that uses an aggregate function; you must use only field names from a table, query, or SQL statement." I've looked at help...

Hi. For simplicity, the table has two fields [location],[type]. SQL = Select loc, type, count[type] AS [number] from table Group BY loc, type; assume percent = count(number)/sum(count(number)) On report GROUP loc Detail type number percent Report Footer...

Hi, again. I find on further inspection that the aggregate function isn't going to work for me since I have to use different date ranges in the query, not necessarily all dates. I would then use a date BETWEEN statement but I don't believe I can use that in an aggregate function.

Thank you for your response. Sorry I didn't ask the question more clearly. The aggregate function DCount gave me what I needed by allowing me to not place the date field in the Select statement. I haven't used those in awhile.

Given the following: Table Date Name Type 01/04 John A 01/04 Ann B 01/05 John B 01/05 John B 01/06 Ann A I use the Distinct function to get a summary report: "Select Distinct name, type, count(type) from table GROUP By name, type;" Name Count Type Ann 1 A 1...

Pretty much does it. Just a little manipulation with the ordering. Thanks.

Using Access97. The data is in a table with date and type of intervention fields. I need is count of type of interventions for each month on a dynamic report - eg the last 12 months. tbintervention date type 1/1/04 ABX 1/1/04 PNE 1/2/04 ABX 2/1/04 ABX 3/1/04 CLA 3/12/04 DOSH...

The program is an item order program originally written in Access97 to run on a single PC. I split the database into frontend and backend (backend on a server and copy of the front on various PC's. The program logic is such that when the user enters the order, an order id is generated from a...

I discovered a solution. If I don't use continuous forms and an array I can populate the forms as I wish. Dim z As Double Dim y As Double Set db = CurrentDb() Set rs = db.OpenRecordset("qryCountTypeMax", dbDenyRead) Me.txtBase.Value = rs.Fields("MAX") y = Me.txtBase.Value Set rs = Nothing sql...

Actually a quasi-bar graph question based on two queries: Form is unbound. I do not wish to use real charting. qryCount= "SELECT DISTINCT tbIntervention.type, " & _ "Count(tbIntervention.type) AS [Number] " & _ "FROM tbIntervention; " qryMax = "Select Max(Number) as [Max] from...

Thanks for responding. I imported all into a new database with same results. For some reason Excel will not run the macro thru automation. I can place a shortcut to the excel spreadsheet on my desktop, run an Access import, click the icon to run the Excel macro, then go back into Access and...

And to add to the above, Chr(97) = "a", Chr(98) = "b" , etc. (just add 32 to upper case to get lower case) rmt

'I had this code running in Access 2000/Excel 2000 'but had to revert to Access 97 due to licensing. 'I am getting an "automation error" when it returns 'to Access. Any thoughts? Thanks in advance !! 'Rick 'Access code: Dim objXL As Object Dim x As Variant Dim boolXL As...