Any MS Access users here? (1 Viewer)

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
772
Age
42
Offline
I'm doing some work with MS Access and have very, very little familiarity with it.

I'm working with a database of bone mineral density scans. Each individual has multiple scans in the database at different time points. There are thousands (70,000 to be exact) of scans, though considerably fewer individuals obviously.

I am trying to limit my search to with certain criteria and I have it down except for one thing: the system treats each scan as an individual entry instead of each patient. So if I do a search and patient A comes up, amongst others, he will come up with 8 entries or so, one for each scan he underwent.

What I want is a way to limit it to the first scan available by date, or some variation of that.

Anyone familiar enough with MS Access to know how to go about that? I can contact the tech guys over here but they take forever, it might be days before I get a response..

Thanks in advance...
 
I can probably help you. Are you searching with a query? You probably want to use "select distinct(patient_id)" or "select min(scan_date)".

With more details of table layout and search methods, I can probably be more help. Feel free to PM me.
 
you have a query with two table in it (one table with their name and one table with their scans)? if so, you need to click on the grouping icon (the sigma) then click Min on the group by under the scan date.

 
I'm doing some work with MS Access and have very, very little familiarity with it.

I'm working with a database of bone mineral density scans. Each individual has multiple scans in the database at different time points. There are thousands (70,000 to be exact) of scans, though considerably fewer individuals obviously.

I am trying to limit my search to with certain criteria and I have it down except for one thing: the system treats each scan as an individual entry instead of each patient. So if I do a search and patient A comes up, amongst others, he will come up with 8 entries or so, one for each scan he underwent.

What I want is a way to limit it to the first scan available by date, or some variation of that.

Anyone familiar enough with MS Access to know how to go about that? I can contact the tech guys over here but they take forever, it might be days before I get a response..

Thanks in advance...

There a couple of different things you can do depending on how your database is structured. I assume you have type of column in your "Scans" table to reference the paitients in the Patients table. One way you can reduce duplicate row returns in a query is to use a sub query for that column instead of a straight join such as follows:

select
p.Patient as 'PaitientName',
(
Select s.Scan
From Scans as s
Where min(s.ScanDate) and s.Patient=p.Patient
) as Scan
from Patients as p


if you want send me a scheme of the datatables and their relationships i can help you with something more specific.
 
Thanks for the suggestions and offers fellas. I'm starting to (slightly) get the hang of it. I made a query and then used that as another table, which made life much easier.

I may be back on here with some more questions. This database is huge but kind of poorly put together. Its been done haphazardly by many different techs over about 8 years...lovely
 
Thanks for the suggestions and offers fellas. I'm starting to (slightly) get the hang of it. I made a query and then used that as another table, which made life much easier.

I may be back on here with some more questions. This database is huge but kind of poorly put together. Its been done haphazardly by many different techs over about 8 years...lovely

sounds like a real mess. if you can generate a image of the schema, i could give so advice on structure and normalization.
 
sounds like a real mess. if you can generate a image of the schema, i could give so advice on structure and normalization.

Thanks for the offer Jeff, I might take you up on it if I can't get this figured out. They have an IS department here and the guy is actually really good, he's just way to busy to be babysitting me all day.

Its frustrating because I understand what im trying to do and I have a decent image of how to go about it, I just get bogged down in little specifics.

I do have one question that might be fairly easy to answer.

I have two databases I'm working with. One is large (some 25,000 individuals) and one is smaller (271 entries).

I'm joining the two tables on the clinic numbers, which are the same for each individual. When I do this, the list of matches is 193 people, which is pretty expected because not everyone from the small database should be in the larger one.

What I want to do is to generate a list of the 78 people who were not matched when I joined the two tables. That way I can take them separately and chase down their data myself.

I know how to pull matches from both or data from one + matches from the other, but I don't know how to show the excluded data from a match.

Any thoughts?
 
Last edited:
copy your smaller table to another table where you can mess with it w/o worry about destroying the data.

join the COPY with the big table and do a delete query, deleting the 193 records from the copy of the smaller table. that'll leave you with just the 78 people who don't match left in the table.
 
copy your smaller table to another table where you can mess with it w/o worry about destroying the data.

join the COPY with the big table and do a delete query, deleting the 193 records from the copy of the smaller table. that'll leave you with just the 78 people who don't match left in the table.


rawk..thanks Ted, that worked like a charm.

Q2:

I now have a list of people, some of whom have multiple scans and some of whom have only 1. I have them listed in a query with each individual scan showing with the persons name.

What I want to do is to eliminate the entries that show ONLY one scan. So I only want to consider people with multiple scans.

Any ideas?
 
Awesome, got it and got the list printed out.

Aaaand next...this one is a bit more complicated.

Ok, on the patients with multiple scans, I have each scan listed and I have a column for a diagnosis date. What I want to do is to take the two scans immediately before the diagnosis date. The problem is that the diagnosis date is of course different for each person, so its a variable issue.

What I tried to do is to format the scan date into yyyy only, because that is the format of the diagnosis date. Then I tried to group them with a "where" condition where I take all scans where "scan date<diagnosis year", but that didn't work.

I know that wouldn't have only given me the last two scans before the diagnosis but I figured it was a step in the right direction.

Any thoughts on a) why that didn't work and b) how I would go about making it work?

Thanks!
 
Hint: "Scandate < Diagnosisdate" (less than) means before and greater than means after. Use your comparison operators. If your dates aren't in a date format (and are numeric), then you will have to do a conversion.
 
Hint: "Scandate < Diagnosisdate" (less than) means before and greater than means after. Use your comparison operators. If your dates aren't in a date format (and are numeric), then you will have to do a conversion.


Right, thats what I want. I want scans that came before diagnosis. We are tracking changes that would occur before the actual diagnosis of the disease.

I just checked the format. The scan dates are in date/time format and the diagnosis dates are in text format. If I change diagnosis date to date/time will I be able to use them both in an operation?
 
Sigh, ok, here's the deal.

I started with an excel sheet. I went back to the excel sheet and reformatted the data so it is in Date format. Now I want to update the Access database that is based on the excel sheet, so that I can then update the linked table that is in the bigger database.

I want to do all of this without somehow ruining all the progress I've made...thoughts?
 
Ok, here is a screen shot of how I have this set up. The dates are in yyyy format and labelled as Date/Time.

What I want to do is to take the Scans (under scan_date) that come before the Diagnosis.

Why can't I just type Scan_Date<DIAGNOSIS p criteria?< under>
 

Attachments

  • ScanScreen.jpg
    ScanScreen.jpg
    37 KB · Views: 4
Last edited:
Sigh, ok, here's the deal.

I started with an excel sheet. I went back to the excel sheet and reformatted the data so it is in Date format. Now I want to update the Access database that is based on the excel sheet, so that I can then update the linked table that is in the bigger database.

I want to do all of this without somehow ruining all the progress I've made...thoughts?

copy the table again with a new name (ensure the data types are what you want them to be), and then you can import the spreadsheet into that table. You may have to change you queries to point to the new table.

Can you send me a full screen shot? wheni try to blow up that image to read it the text blurs to much. you can send it to

jeffrey.miller@steelscape.com

Are you using only the designer to build your queries or are writing your own using Sql?
 

Create an account or login to comment

You must be a member in order to leave a comment

Create account

Create an account on our community. It's easy!

Log in

Already have an account? Log in here.

Users who are viewing this thread

    Back
    Top Bottom