query to only show if ID is matching in other table

[Elmobram22]

Hi all,

I have two tables. One with a persons name and ID, DOB, GP etc (TblName) and one with the persons ID and medication they take (TblMedication). I have a query that runs a report. The header is made up of the first table (TblName) and the body of the second table (TblMedication). All works fine but it shows all records from the first table (TblName) regardless of whether they have medication or not so the body shows up blank. I want to run the query with both tables information but only show the persons header if they have medication. Does that make sense?

Cheers, Paul

 

[CJ_London]

modify the recordsource for your main report along the following lines

SELECT *
FROM tblPatients
WHERE Exists(SELECT * FROM tblMedication WHERE personID=tblPatients.personID)

 

[vbaInet]

If I'm understanding the requirement, an inner join would suffice here. Create a query based on the two tables and join them via the ID fields. That will ensure that only patients with at least one medication will pull through.

 

[Elmobram22]

Inner join deos work but the one thing I forgot to say is I have a tick box on each medication to say whether a report is required or not. So like say they have 5 medications and two need to be shown or 5 medications and none need to be shown. Just tried it now and it just shows a blank body. Any more ideas?

 

[CJ_London]

SELECT *
FROM tblPatients
WHERE Exists(SELECT * FROM tblMedication WHERE personID=tblPatients.personID AND tickBox=true)

 

[Elmobram22]

Hi,

Can't really get this to work so I have made a stripped down version of the database and uploaded it here. You will see when you open it that there are two buttons on the main form. One to edit the medication and one to print preview the report. If you run the report it asks you to put a date in (just pick any doesn't matter). You'll see that Peter Bramwell has an empty body to his report so I effectively don't want it to print his header with no info. The reason for this is the tick box "Not Required In Tray" which can be found when editing the information. Any help would be greatly appreciated.

Cheers,

Paul

 

[Elmobram22]

Here is the file

 

[Elmobram22]

I did this database when I was learning so I know the data isn't the way it should be just need a quick fix to this

 

[RainLover]

I did this database when I was learning so I know the data isn't the way it should be just need a quick fix to this

A quick fix will not solve your problems here.

You are going to have one problem after another.

Fix your relationships first and you will be much better off.

PS

Please do not use reserved words like "Name". This will also get you into trouble.

PSS

It is likely that the previous suggestions won't work either unless you fix the relationships.

 

[CJ_London]

in your query QryResMedAlpha

put the following in a new column

Exists (SELECT * FROM QryResMedAlpha WHERE Resident=tblResident.ID)

then untick the 'show' box

and in the criteria put True

 

[Elmobram22]

Hi,

I have rectified the data in the tables now and also tried your suggested fix CJ but couldn't get it to play. I have re-uploaded the file here...

 

[Elmobram22]

Here's the file

 

[Elmobram22]

Comes up with a circular reference problem when I do the work you suggested CJ

 

[CJ_London]

my mistake this needs to go into query QryResNameAlphaCurrentMeds - the recordsource to RptMar

 

[Elmobram22]

Nah I still can't get it to work. Now its asking for the TblResident.ID and when I add that into the other query it takes away from the idea of it anyway and just shows them all

 

[CJ_London]

the reason it won't work is because you have changed QryResMedAlpha

restore it to what it was and it will work

 

[Elmobram22]

Then I get enter parameter TblResident.ID

 

[Elmobram22]

When I run the report

 

[CJ_London]

you've probably changed the report as well

 

[Elmobram22]

No they are both the same. Just keeps asking for that parameter even though it is there. Only seems to happen when I add the EXIST in