What type of query?

[Linda RQ]

Hi Everyone,

I have records in my Access Database Version 9.0 with these basic fields,
LName, FName, PtNumber, AdmitDate, DCDate. The Names and PtNumber are
already entered and there are 2000 records. Each week I get a report that
has all of our dicharged patients with many fields including PtNumber,
AdmitDate and DCDate populated (about 100 records a week). So far we are
manually entering these records. I think I read somewhere that info from
excel can be imported to access so I am wondering how I can do this. I used
a dataminer program (Monarch) and exported these fields AcctNum (which is
the same as my PtNumber), AdmitDate and DcDate into an excel Version 9.0
spreadsheet. Now I need to marry the records. Can this be done?

Thanks,
Linda

 

[Cindy]

Hi Linda -
The wedding won't be too costly <g>.

If you haven't worked with update queries before, back up your table
first just in case. Never a bad idea anyway.

Assuming your Excel spreadsheet has column headings and no blank rows,
link the excel file to your access dbase using the menu option File,
Get External Data, link tables.
Change the file type (bottom of the dialog box) to Excel, and select
the file with your excel data.

This Excel file is now "married" to the database, and available to you
within queries.
Create an update query matching the two "tables" on the PT number.

To do this - first create a normal query using the excel "table" and
your Access table - linked on PT number (hate assumptions, but this
also assumes that the PT number is a primary key and there are no
duplicates of it in the Excel file). Bring down the discharge date
from the ACCESS table. Be sure you bring it from the Access table and
NOT the excel table, because we want to bring into the grid the field
that needs to be modified.

Then change the query type to UPDATE (menu option under the query menu
item). This will add a new row to the bottom grid area called Update
to. Right mouse click in this cell under the Access discharge date
(across from the words update to) - select the build option, and point
and click your way to the excel table name and the DcDate field. If
your excel file was called Sheet1 (likely), it will look something
like this: [Sheet1]![DcDate]

Be careful of case - I believe a linked Excel file is case sensitive
even though Access is not.

Across from the row labeled "Criteria" enter IS NULL so that ONLY
records that don't already have a discharge date get updated.

To perform the update, click the red exclamation point in the
toolbar. It will warn you that XX records are about to be updated.
After clicking ok - the change is done. Save the query in case you
want to re-use it every time you get a new excel file.

(If you name the excel file exactly the same, you can probably just
replace the excel file with the new one each time and then re-run this
query. OR, better yet, write a VBA routine that checks for the file,
does the link, and does your update with one click of a button each
month - or whenever.)

 

[Linda RQ]

Thanks, Cindy!

I just skimmed as I am on my way to my other job....."Mommy Taxi" then
home...I hope I can do this before my volunteer comes over in the morning to
read to me while I enter the data...then we can both just sleep in instead!

Don't worry, I have all kinds of backups <g>

Linda

Hi Linda -
The wedding won't be too costly <g>.

If you haven't worked with update queries before, back up your table
first just in case. Never a bad idea anyway.

Assuming your Excel spreadsheet has column headings and no blank rows,
link the excel file to your access dbase using the menu option File,
Get External Data, link tables.
Change the file type (bottom of the dialog box) to Excel, and select
the file with your excel data.

This Excel file is now "married" to the database, and available to you
within queries.
Create an update query matching the two "tables" on the PT number.

To do this - first create a normal query using the excel "table" and
your Access table - linked on PT number (hate assumptions, but this
also assumes that the PT number is a primary key and there are no
duplicates of it in the Excel file). Bring down the discharge date
from the ACCESS table. Be sure you bring it from the Access table and
NOT the excel table, because we want to bring into the grid the field
that needs to be modified.

Then change the query type to UPDATE (menu option under the query menu
item). This will add a new row to the bottom grid area called Update
to. Right mouse click in this cell under the Access discharge date
(across from the words update to) - select the build option, and point
and click your way to the excel table name and the DcDate field. If
your excel file was called Sheet1 (likely), it will look something
like this: [Sheet1]![DcDate]

Be careful of case - I believe a linked Excel file is case sensitive
even though Access is not.

Across from the row labeled "Criteria" enter IS NULL so that ONLY
records that don't already have a discharge date get updated.

To perform the update, click the red exclamation point in the
toolbar. It will warn you that XX records are about to be updated.
After clicking ok - the change is done. Save the query in case you
want to re-use it every time you get a new excel file.

(If you name the excel file exactly the same, you can probably just
replace the excel file with the new one each time and then re-run this
query. OR, better yet, write a VBA routine that checks for the file,
does the link, and does your update with one click of a button each
month - or whenever.)

Hi Everyone,

I have records in my Access Database Version 9.0 with these basic fields,
LName, FName, PtNumber, AdmitDate, DCDate. The Names and PtNumber are
already entered and there are 2000 records. Each week I get a report
that
has all of our dicharged patients with many fields including PtNumber,
AdmitDate and DCDate populated (about 100 records a week). So far we are
manually entering these records. I think I read somewhere that info from
excel can be imported to access so I am wondering how I can do this. I
used
a dataminer program (Monarch) and exported these fields AcctNum (which is
the same as my PtNumber), AdmitDate and DcDate into an excel Version 9.0
spreadsheet. Now I need to marry the records. Can this be done?

Thanks,
Linda

 

[Linda RQ]

Hey, Beaners has Wi-Fi...so I get to work on this a little while I wait.

To do this - first create a normal query using the excel "table" and
your Access table - linked on PT number (hate assumptions, but this
also assumes that the PT number is a primary key and there are no
duplicates of it in the Excel file). Bring down the discharge date
from the ACCESS table. Be sure you bring it from the Access table and
NOT the excel table, because we want to bring into the grid the field
that needs to be modified.

.....RUT-ROH...

The PT number is not the primary key and there are duplicates of it in the
excel file. The primary key in that table is an auto number. We start a
patient record when they are started on therapy. They may and usually end
their therapy before they are discharged from the hospital. Once therapy is
stopped, the record is hidden from the users and there are blank records
until I enter it. I get a weekly report of discharged therapy patients and
need to get the hospital admit date and discharge date in with the record.
I guess I should have made a seperate table and join it by pt number
but...whoops. I was hoping that there was some way for me to paste the
admit and discharge date on the correct record.....kind of like look for
"4325323" and paste the Admit and DC date from that row on the excel sheet
to the access table/query. If I did this manually, I could find the correct
patient number and copy just the Admit and Discharge dates from the excel
file into the current blank fields to complete the record.

Linda

 

[Cindy]

Hmmm - As long as you only have one open admission at any given time,
you might still be okay - adding the criteria is null to the discharge
date will limit it to just the open record - right?

 

[Linda RQ]

Since I am using a copy of my database, I am trying this. First of all, your
instructions are really great. I got everything done and hit the "Run" and
got the following error.

My database is split into front and back ends. At first I linked the excel
file to my back end and quickly discovered it wasn't available from my front
end query grid so I linked it to my front end and everything worked smoothly
until.

A JOIN expression is attempting to join two tables on fields of incompatible
data types. For example, you will get this error if you attempt to join a
Memo field with a Text field.

My 2 join fields are the patient number but they are called different things
but I don't think that matters. Acct(excel sheet) PtNum(access table). I
looked and the properties of my PtNum field is text. The properties of my
Acct. is Number. I tried to change it in the database but it didn't work.
I changed it to text in the excel sheet and repeated the process of making
the append query but got the same error. The excel sheet field says text
and the linked excel sheet still shows number.

Any ideas?

Thanks,

Linda

 

[Linda RQ]

Ok...I used the forumla below in my excel sheet and strangely enough, my new
field is now a text field in my linked excel table so I can now run the
query.

=TEXT(D2,"0")


My next question is, I would like to see which fields were updatae to make
sure this is working right. When I click datasheet view with just the DC
Field, I can see all the dates to be added but I would like to see the last
names from my tblPatients. I add that field to my query grid but it doesn't
show up.

 

[Cindy]

To view the records before doing the update, you have to change the
query to be a SELECT query. Then switch it back to an update query as
needed.

Update queries are the only ones that won't really give you a "sneak
preview" - for most others, you can use the view button before running
to check it out (works for delete queries and make table queries).

One nice thing about this though is that somewhere in its bowels,
Access stores the information you've typed for the update -when you
swap it to a select query, the Update to line disappears, but it will
re-appear WITH whatever you typed in it once you swap it back.

 

[Linda RQ]

Thank you so much...This is going to work! You are a really good teacher.

Linda