Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access Form Display related data


(!)

Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
04-Jul-2012, 05:31 PM #1
Solved: Access Form Display related data
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?
Attached Files
File Type: zip Backup 2.zip (296.7 KB, 6 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
06-Jul-2012, 12:37 PM #2
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.
__________________
OBP
I do not give up easily
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
10-Jul-2012, 05:49 PM #3
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's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
10-Jul-2012, 05:57 PM #4
Current Version
Here is the current version
Attached Files
File Type: zip Backup3.zip (445.6 KB, 4 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
11-Jul-2012, 06:51 AM #5
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's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
11-Jul-2012, 01:52 PM #6
Thanks OBP, it says under options that it saves it as 2007 as standard...is it not opening?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
11-Jul-2012, 02:04 PM #7
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
11-Jul-2012, 02:08 PM #8
Nope, can't open it.
Here is a blank Access 2007 database, can you open it and then import the objects from your database?
Attached Files
File Type: zip Backup3.zip (8.2 KB, 5 views)
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
11-Jul-2012, 03:08 PM #9
Doing that now...
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
11-Jul-2012, 03:14 PM #10
Database
Here it is...thanks again!
Attached Files
File Type: zip Blank Backup3.zip (230.9 KB, 3 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
11-Jul-2012, 03:41 PM #11
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's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
11-Jul-2012, 04:17 PM #12
tried saving mine as 2000...didn't seem to change anything?! Should there have been a database on last post?
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
11-Jul-2012, 04:20 PM #13
I wonder if it's because I have it loading into Main menu?
Here it is without...
Attached Files
File Type: zip Blank Backup3.zip (231.2 KB, 5 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,592 posts.
 
Join Date: Mar 2005
Location: UK
12-Jul-2012, 05:24 AM #14
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.
Attached Files
File Type: zip Updates.zip (8.5 KB, 6 views)
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
12-Jul-2012, 07:25 AM #15
random....here it is...the qry is Condition Tally...thanks again!
Attached Files
File Type: zip OBP.zip (175.1 KB, 3 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑