Access query return DOB

[Seattle5674]

Hello,

I am looking to make this query return the AVG DOB per house name.

When I run the query I get this mesage with zero results.

I want the query to just return the single house name with the DOB from the clients table.

Any suggestions?

[ranman256]

are you sure HouseID and ClientID are the same?
Seems like t.HouseRooms would have a ClientID field.

[Micron]

If al l you want is records to be returned, don't create a make table query; use Select instead. If you get no records, then there is nothing in the fields that are joined that are equal. Also, you should not store calculations in tables (e.g. age), rather, calculate in forms and reports when needed.

[Gicu]

I think actually you should add a HouseID_FK (foreign key) to the Clients table and join that to HouseID (notice the field name has no space in it) in tblHouseRooms (again no space or special characters in table name) as you would have multiple clients living in the same house.

Cheers,

[Seattle5674]

Thank you Gicu, I did that. However, it is returning zero results. I have a screenshot of the design query. Is there something I need to fix there?

[Gicu]

Can't say without seeing your data but you should not have the last field in the query.

Cheers,

[Gicu]

Please see attached sample.

Cheers,

[Seattle5674]

Thanks Cicu, that worked. Except how do I format decimals? the results are returning many decimals.

I want to update at design level not table level.

[Gicu]

Open the query in design view, select the calculated field (Age) then go to Properties (or right click on the fields and select properties in that menu) and under format select Fixed and In Decimals select 0 (or 1 or whatever you want).

Cheers,

[Seattle5674]

I tried that but the decimal places box is not showing up under properties.

[Gicu]

Fixed not Standard.
Otherwise just wrap the whole expression in FormatNumber() function:

AGE: FormatNumber(Avg(DateDiff("yyyy",[ClientDob],Date())),2)

https://support.microsoft.com/en-us/...7-311ab6ebf43b

[Seattle5674]

that did the trick. Wow, its much easier to just wrap in the code that mess with properties.