[Erm]

I apologise in advance but although I was once fairly proficient in Access, I have had a baby and not touched it for over 2 years and am now using 2010 instead of 2003 and feel like my Grandma would trying to send an email!

I have a really daft issue...

I have a basic database, Client table, Treatments table and various lookup tables.

I have a form to view/enter Client table records (frmClientandTreatments) twith a subform showing related Treatments. I store a Condition (e.g. Arthritis) in the clients table which is looked up from another table that provides the category (e.g. Muscloskeletal)...I want to have the category display on the Client form when a Condition is selected. How do I do this as the record source of the form is the Client table? I tried setting up a query as the record source but themn the combo boxes didn't seem to work and it wouldn't let me add new records...I am confused!

Also, should I create relationships to lookup tables? Enforce referential integrity?

[OBP]

I am not surprised that you are Confused. Access 2010 &2007 do things completely different to 2003, and not necessarily better that it makes life very difficult.
I haven't looked at your database yet but I would suggest that you actually need 3 tables.
Client table
Condition Table
ClientCondition table, which has a 1 to many from the other 2 tables, it would contain the ClientID and the ConditionID key fields.
That way you can have more than one condition per client.

[Erm]

It really is like starting over moving to 2010! Glad it's not all me! =D I have Now store primary condition and condition 2,3,4. This is always going to suffice but I do a count of each Condition Type in all these fields and am trying to get it to add up and it isn't working...this is the SQL
SELECT lkpCondition.ConditionCategory, lkpCondition.Condition, [qryCountPrimaryCondition]![CountOfCondition]+[qryCountCondition2]![CountOfCondition2]+[qryCountCondition3]![CountOfCondition3]+[qryCountCondition4]![CountOfCondition4] AS TotalTally
FROM qryCountPrimaryCondition, qryCountCondition4, qryCountCondition3, qryCountCondition2, lkpCondition
GROUP BY lkpCondition.ConditionCategory, lkpCondition.Condition, [qryCountPrimaryCondition]![CountOfCondition]+[qryCountCondition2]![CountOfCondition2]+[qryCountCondition3]![CountOfCondition3]+[qryCountCondition4]![CountOfCondition4]
ORDER BY lkpCondition.Condition;

[Erm]

Here is the current version

[OBP]

Sorry, I can't work with Access 2010, can you save or convert it to Access 2007?

I am not very good at analysing query in SQL mode, so I really need to see the query.
It may be that you need a second query to do what you want.

[Erm]

Thanks OBP, it says under options that it saves it as 2007 as standard...is it not opening?

[OBP]

No I have had this problem a few times now.
I will see if I can change anything if not I will attach a blank Access database and you can import the objects in to it.

[OBP]

Nope, can't open it.
Here is a blank Access 2007 database, can you open it and then import the objects from your database?

[Erm]

Doing that now...

[Erm]

Here it is...thanks again!

[OBP]

Nope, I still can't open it, I had this recently and can't remember how I got around it.
maybe an Access 2000 database might do it.
Try this one, ignore anything that might be in it..

[Erm]

tried saving mine as 2000...didn't seem to change anything?! Should there have been a database on last post?

[Erm]

I wonder if it's because I have it loading into Main menu?
Here it is without...

[OBP]

Yes there should have been a Database attached to my last post. i have had that happen a few times now.

Your last version is the same as the others, I think it is due to a setting, but I can't remember what it was.

Here is a 2000 database.

[Erm]

random....here it is...the qry is Condition Tally...thanks again!