Any MS Access users here? (1 Viewer)

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
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...
 

buzd

party lamp
Staff member
Tech-Admin
Joined
Jan 11, 2002
Messages
33,888
Reaction score
29,192
Location
Duncan Plaza
Offline
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.
 

Ted

muddlin'
Gold VIP Contributor
Joined
Aug 1, 1997
Messages
13,281
Reaction score
1,236
Age
57
Location
Bethesda, MD
Offline
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.

 

Jeff Miller

Shaw 1988, NYIT 2009
Joined
May 27, 2002
Messages
18,512
Reaction score
9,040
Age
51
Location
Vancouver, WA
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...

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.
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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
 

Jeff Miller

Shaw 1988, NYIT 2009
Joined
May 27, 2002
Messages
18,512
Reaction score
9,040
Age
51
Location
Vancouver, WA
Offline
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.
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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:

Ted

muddlin'
Gold VIP Contributor
Joined
Aug 1, 1997
Messages
13,281
Reaction score
1,236
Age
57
Location
Bethesda, MD
Offline
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.
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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?
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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!
 

buzd

party lamp
Staff member
Tech-Admin
Joined
Jan 11, 2002
Messages
33,888
Reaction score
29,192
Location
Duncan Plaza
Offline
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.
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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?
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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?
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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:

Jeff Miller

Shaw 1988, NYIT 2009
Joined
May 27, 2002
Messages
18,512
Reaction score
9,040
Age
51
Location
Vancouver, WA
Offline
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?
 
OP

Saints#34

Hall-of-Famer
Joined
Jul 8, 2001
Messages
4,315
Reaction score
771
Age
39
Offline
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?


Oh my no...no Sql writing here, only designer. I wouldn't even know how to begin writing using my own Sql. I will email you a full screen shot.
 

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

 

New Orleans Saints Twitter Feed

 

Headlines

Top Bottom