Seek Multiple Results in Query ?

[Rick4077]

How do I get a separate answer (Avg and count) for "SFR" and "CONDO" in this query?

Is there a way to use DAvg() instead of Avg?

Thanks . . .

SELECT Avg(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICEAvg, 
Count(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICECount
FROM OCDownloadRES
HAVING (((OCDownloadRES.CITY) Like "RSM") AND ((OCDownloadRES.PROPSUBTYPE) Like "*" & "CONDO") AND ((OCDownloadRES.PENDINGDATE) Like "2006-07" & "*")) OR (((OCDownloadRES.CITY) Like "RSM") AND ((OCDownloadRES.PROPSUBTYPE) Like "*" & "SFR") AND ((OCDownloadRES.PENDINGDATE) Like "2006-07" & "*"));

Rick

 

[dhookom]

I don't understand why you have [blue]City Like "RSM" [/blue]without any wild card characters. It would be more efficient to use [blue]City = "RSM" [/blue]. If PENDINGDATE is actually a date/time field, I would not query it using a wild card and text/string expressions.

This SQL might work:

SELECT Right(PROPSUBTYPE,3) as SubType,
Avg(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICEAvg, 
Count(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICECount
FROM OCDownloadRES
WHERE 
(OCDownloadRES.CITY Like "RSM" AND OCDownloadRES.PROPSUBTYPE Like "*" & "CONDO" AND OCDownloadRES.PENDINGDATE Like "2006-07" & "*"
) OR 
(OCDownloadRES.CITY Like "RSM" AND OCDownloadRES.PROPSUBTYPE Like "*" & "SFR" AND OCDownloadRES.PENDINGDATE Like "2006-07" & "*")
GROUP BY Right(PROPSUBTYPE,3);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

And what about this ?
SELECT PROPSUBTYPE, Avg(PENDINGPRICE) AS PENDINGPRICEAvg, Count(PENDINGPRICE) AS PENDINGPRICECount
FROM OCDownloadRES
WHERE CITY = 'RSM' AND (PROPSUBTYPE Like '*CONDO' OR PROPSUBTYPE Like '*SFR') AND PENDINGDATE Like '2006-07*'
GROUP BY PROPSUBTYPE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Wow! Both of these two suggestions worked perfectly and produced the same results.

"I don't understand why you have City Like "RSM" without any wild card characters . . "

Because RSM is the correct city code and I only want properties that are in RSM.

Using PENDINGDATE Like "2006-07&"*"is because I want only those properties in JULY of 2006.

Unfortunately my date format for this database is "yyyy-mm-dd." It makes it easy to select years and month. I never use days in this query. Is there an easy way to make "yyyy-mm-dd" into "mm-dd"yyyy"?

I hope that answers the questions.

You two are awesome.

Thanks much. Rick

 

[Rick4077]

Okay what about this. . . .

How can I have the query stop and ask me to enter city codes, dates and other criteria?

This would be kewl!

Thanks much . . .

Rick

 

[dhookom]

If you only want City values that equal RSM then use " = " rather than " Like ".

I didn't need to know the "date format", only the data type of the value. If the field is a date/time data type then you might consider using:
Format([PENDINGDATE],"mmyyyy") = "072006"
This expression explicitly converts your date field to a text field and then compares it to a text field. You could also use
Year(PENDINGDATE)=2006 AND Month(PENDINGDATE)=7
These solutions both depend on the data type of PENDINGDATE being date/time which you haven't confirmed or denied.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

FYI - only use LIKE when you're not sure if the field is an exact match:

LIKE 'AC*' will return all records where the field STARTS with Ac -
Account
Access

LIKE '*AC' will return all records where the field ENDS with Ac -
Lilac
Prozac

LIKE '*AC*' will return all records where the field CONTAINS Ac -
Account
Access
Grace
Lilac
Prozac

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Rick4077]

Thanks Duane for the "Like" info.

The data type for all of my date fields are set to text. It's unfortunate but when I import this file the date fields have import errors every time so I set them to text.

When I try and reset the data type in table/datasheed design view after the import I get this.

Error . . .

"Microsoft Access can't change the data type. There isn't enough disk space or memory."

All I'm doing here is changing a known date field to dateTime field and from 255 to 10 Length.

Table is 54 fields, 214,081 records. Automatic field length for most all of the fields is 255. Seems large to me.

I've tried compacting the table, then changing the data type. No change! In fact, if I try and chane data type or field length for ANY field I get the same error message.

I have Access 2003
2 Gig RAM
200 GB hard drive 85 percent free! (Two of them actually!)

What do you think?

Thanks . . . Rick

 

[dhookom]

Sorry to hear that your field is text and not Date/Time which has more functionality.

You might want to create a new table with a similar structure except a real date field. Then append the records from your one table to the new table. Whether a text field is set to 20 characters or 200 characters has little effect on the mdb.

I would normally comment on "54 fields" but you suggest the table is imported from another system.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rick4077]

I understand about the number of fields in a table.

This database has 241 fields. I just chose the absolute minimum for my work.

I also understand the advantages of having a date formatted date field.

Why won't Access 2003 import these date/time fields?

Thanks much.

Rick

 

[Rick4077]

Correction . . .

I was able to import the database again and set the date fields to Date/Time.

Don't know what it was but it's okay now.

Thank you all for your assistance.

Rick