1) Adding people twice to a report. 2)Grouping and Sorting on a report

[varich]

Thank you to those who helped me with my first question on TekTips last month. It got me moving in the right direction. I’ve nearly completed my project to the stage where it can start being tested! A bit of history. I am developing a database that will track veteran patients in community hospitals. Most of those in the community hospital should be transferred to our hospital. We receive a call from the community hospital notifying us that the veteran has been admitted. We then put him on the “Tranfer List” which has been an excel spreadsheet. We (I) are now developing a database to track this program and try to provide better service. I have created two entry forms, one for the business office tracking the $$$ and one for the transfer office tracking the veterans. I have one report, a transfer list that is used daily. The issue is that somehow, the transfer report adds people to the report twice. Not every person, and not every time. The other issue is that I need to group these people, first by the date we enter them on the list, and then by their list status. How do I fix the problem of people appearing twice, and how to I group/sort? them on the list?

 

[lameid]

It sounds like you have a table of people and a table of stati. For reporting purposes you joined them. The query is showing the person once for each stati.

You can fix this by grouping in the report and putting the person in the person report header and leaving the stati in the detail section.

However, this may not be what you really want. Do you want all stati or just the most recent or???

It would really help if you swiched the query to SQL view and posted the SQL statement here.


Grouping in reports in general can be done by right clicking the little black square where the two rulers meet in the report designer. On that drop down select Sorting and Grouping. To Group you have to specify you want a header / footer.

 

[varich]

lameid, here is the SQL view. Just so you know, I am very new at this.

SELECT TransferMaster.AdmitDate, TransferMaster.AdmitTime, VeteranMaster.LastName, VeteranMaster.FirstName, VeteranMaster.LastFourSSN, VeteranMaster.DOB, VeteranMaster.PCP, BedType.BedType, TransferMaster.FeePay, VeteranMaster.[%SC], VeteranMaster.Insurance, TransferMaster.CPRSDate, TransferMaster.CPRSTime, TransferMaster.AcceptBy, TransferMaster.AdmitDX, TransferMaster.Hospital, TransferMaster.HospitalPhone, TransferMaster.Ward, TransferMaster.WardPhone, TransferMaster.CaseMgrName, TransferMaster.CaseMgrPhone, TransferMaster.MDName, TransferMaster.MDPhone, TransferMaster.StatusReport, [AdmitDate] & " " & [AdmitTime] AS AdmitComb, [LastName] & "," & [FirstName] & " " & [LastFourSSN] & " " & [DOB] AS VeteranComb, [Insurance] & " " & [VeteranMaster]![%SC] AS FeeInsComb, [Ward] & " " & [WardPhone] & " " & [CaseMgrName] & " " & [CaseMgrPhone] & " " & [MDName] & " " & [MDPhone] AS ContactComb, [CPRSDate] & " " & [CPRSTime] AS CPRSComb, TransferMaster.OffList, TransferMaster.TXFRPriority
FROM (PercentSC INNER JOIN VeteranMaster ON PercentSC.PercentSCID=VeteranMaster.[%SC]) INNER JOIN (BedType INNER JOIN TransferMaster ON BedType.BedTypeID=TransferMaster.BedType) ON VeteranMaster.VeteranID=TransferMaster.VeteranID
WHERE (((TransferMaster.OffList)=False));

 

[lameid]

So you have four tables in this query...

TransferMaster
PercentSC
VeteranMaster
BedType

At least one of these relationships is a 1 to many relationship. That is it is one table once but the same identifier can be in another table multiple times. The only thing that is not somewhat intuitive to me is PercentSC. What is it?

Clearly you could have multiple transfers for the same person. Is this the source of the issue? Maybe you have multiple PercentSC's per veteran or repeated bedtypes in the bedtype table? In any case when you understand the issue, figure out whether you need to show the related information or how you know what information you want to show (I can help with the query just what methodology do you need).

 

[varich]

Percent SC is the level of their service connected disability. From NSG, or not service connected to 100% SC. I have a total of 13 tables. Yes, you may have multiple transfers.
I have 13 tables. The veteran data is in one table, LastName, FirstName, etc. data about the veteran that will not often change. The transfer table contains all the information about the transfer of the veteran, the name of the admit time and date, diagnosis, MD, etc. Then the last “fluid” table contains the billing data. There are some look up tables containing lists of hospitals, the percentsc, reasons off list, infection types, etc. What we need to display in the first report is the transfer list. This contains some, but not all the information about the veteran that is collected on the transfer form. This is used 24 hours per day. Then there is a form that is used by our FeeBasis office. They are the ones who receive a bill from a hospital telling us how much we owe them for the veterans care while the veteran was there. There is no report for them at this point, they have not put in what they need for it. I wish I could show you the tables and the relationships because I think you will see where my problem is pretty quickly. It’s not a large, complicated database, but really my first one.
Each field is unique in each table, but yes, record 1 may have the same bed type as record 4. Does this help?

 

[lameid]

I understand better but it does not get at the root of the problem.

For a Veteran that has duplicates, can you look at the data and see which field is differnt OR which table has a duplicate record? The field that has something different would be the table where the record is twice. For example you might have the same Veteran twice or multiple times in the transfer table. This would cause veterens to appear multiple times. This may or may not be a problem. I suspect not because if the ambulance takes the patient to the nearest hospital, there may be multiple transfers for the chronically ill in a short period. On the other hand, you may be seeing duplicates for some other reason. Likely you will want some sort of date criteria so you are only looking at resent transfers. So if Bill is transferred a year ago and then again today, you only see one transfer.

 

[varich]

I only have 13 people in the database, so it is very easy to see who is in each table. I'm thinking, and I don't know if this is an issue or not, but I think I may have pulled some information for the report from a query, and some from a table. I know I only have one record per person, but as I said, some of the informatino for each record may be the same. For example, veteran John Smith may be in the same type of bed as veteran John Doe, so that informatino is the same, but there is only one John Smith and only one John Doe in the database.

 

[lameid]

The fact that you are joining both on person and bed suggests that the bed is not the source of the duplicate.

The duplicates of people, is all of the reocd the same, if not what is different? What tables are not different and which one is different?

 

[varich]

Actually, the item that is different seems to be the PCP. However, I created a new form last night, and this time it worked. What I am thinking is that my query was messed up. I combined fields, but think I also kept the un-combined fields. Anyway, I'm plugging along. I kept the bad form so that at some point, I can go in and find out what I did wrong. Thanks for all the help.