Microsoft [Cr]Access: Query/Reporting Problems

[dogwomble]

And a good afternoon to everybody out there in ZGeekland.

I've got a rather nasty problem.

I'm trying to put a mini-prototype database together for an Old Granny's Home to keep vital information for their residents. Done in Microsoft Access - it's going to eventually have an interface tossed over it in something like Visual Basic or something like that.

NEways, I'm having problems with a report. Whenever I run the report over a single table, it brings up all records in that table fine - as you'd expect When you run it over multiple tables (which is what is needed as it is designed to give the full information about a resident so it can be easily viewed and printed) you can only ever get information about the first client in the database. The first and only the first and no others. Despite the fact that I have a second "dummy" person in there.

I've tried putting it in with a query as well and querying upon the surnames. This [B}should[/B] display a report for only that person - I've done this to solve the problem and it would also be more useful to the client if it were done this way. If you do a query on the first person in the database, they come up fine. Query the second person in the database, and it doesn't work - just gives back a blank report even though that person does exist in the database.

I'm at a loss as to what could be wrong.

My first thought was to patch up the relationships between the tables but that all seems fine and actually makes no difference.

Seeing as the database only contains test data and not live data, I am able to send it to someone to take a closer look.

Any takers?

[t101]

First things first - what's your query?

[dogwomble]

OK, I tried without a query first - only first record of the database.

Second: With a query, with clients' surname filter est to "Like "*"" to try and select everything. Only first record.

Third: With a query, asking for a prompt for the person's surname. Surname of first person in database = Fine. Surname of second person in database = Doesn't show even though surname known to be correct.

I smell Access giving someone grief or me severely fucking something up - likely the second because I haven't used Access in a while and I'm having to relearn a lot of it as I go.

Version: Access XP saving in Access 2000 format.

[t101]

Must...have...SQL...to...look...at....!

Is your query something like:

select distinct(*) from table1 as t1 left join table2 as t2 on (t1.id = t2.id) where last_name = 'smith';

?

BTW - are you sure you mean report and not form? Reports from what I remember are like summaries and are not particularly dynamic, but forms can allow you to scroll through results (they basically are good for building search queries in a GUI)?

[dogwomble]

Well, a report is probably wha tI'm after - the idea is to put in a person's name then it comes up with a printable version of their file.

The query ends up being *deep breath coz there's a lot of fields*:

Edit: Would it be easier if I emailed you the database?

SELECT tblClientInfo.ID, tblClientInfo.Surname, tblClientInfo.GivenName, tblClientInfo.PreferredName, tblClientInfo.DOB, tblClientInfo.Sex, tblClientInfo.MedOfficer, tblClientInfo.Diagnosis, tblClientInfo.DateOfAdmission, tblClientInfo.AdmissionObservations, tblClientInfo.Weight, tblClientInfo.BP, tblClientInfo.TP, tblClientInfo.Pulse, tblClientInfo.Resp, tblClientInfo.[U/A], tblClientInfo.VGL, tblClientInfo.Religion, tblComms.RelID AS tblComms_RelID, tblComms.ClientID AS tblComms_ClientID, tblComms.HardOfHear, tblComms.HearingAid, tblComms.PutInHearAid, tblComms.AdjustHearaid, tblComms.Glasses, tblComms.GlassesRead, tblComms.CleanGlasses, tblComms.LargePrint, tblComms.LegBlind, tblComms.Reading, tblComms.Dementia, tblComms.ESL, tblComms.Aphasia, tblComms.AssistWriting, tblComms.TelephoneMsg, tblComms.TelephoneAmp, tblComms.tblCommsComment, tblMobil.RelID AS tblMobil_RelID, tblMobil.ClientID AS tblMobil_ClientID, tblMobil.WalkStick, tblMobil.WheelieFrame, tblMobil.ForearmFrame, tblMobil.Wheelchair, tblMobil.BedRails, tblMobil.LiftingMachine, tblMobil.MobileShower, tblMobil.ElevatedChair, tblMobil.EasiReach, tblMobil.ShideSheet, tblMobil.StaffAssist1, tblMobil.StaffAssist2, tblMobil.Assist1, tblMobil.Assist2, tblMobil.ChairOutIn, tblMobil.PositionWalking, tblMobil.tblMobilComments
FROM (tblClientInfo INNER JOIN tblMobil ON tblClientInfo.ID = tblMobil.ClientID) INNER JOIN tblComms ON tblClientInfo.ID = tblComms.ClientID
WHERE (((tblClientInfo.ID)=[Code of person to report on:]));

[t101]

Oh dear god - full table names!!! That's why I love aliases :)

Hmm. OK, I submit - lemme see the DB :)

Check your PMs

[dogwomble]

On it's way...modem transmit once, modem transmit twice, sold to the guy in the pink underwear.

[t101]

My suspicions were correct:

Change INNER join to LEFT join.

God I rock :)

[dogwomble]

If we ever meet in a pub, then I owe you a beer.

[t101]

Mmmm - beer.

NP :)