Update query issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

patti - 24 Apr 2008 20:45 GMT

I wish to update the MedRecord in Table IntakeInfo with
thePatientMedicalRecord# from the Table PatientInfo. PatientID is the pk in
PatientInfo. There is a one-to-many between PatientInfo and IntakeInfo.

I used the query design. This is the SQL code that Access provided:

UPDATE PatientInfo
INNER JOIN IntakeInfo
ON PatientInfo.PatientID = IntakeInfo.PatientID
SET IntakeInfo.MedRecord = [PatientInfo].[PatientMedicalRecord#];

I got nothing. Could someone please help?

Reply to this Message

Klatuu - 24 Apr 2008 21:31 GMT

What do you mean by "I got nothing."

Have you looked at the data to see if the field has been updated?
An update query doesn't open a datasheet like a select query, so you won't
see anything.

The SQL appears to be correct.

Signature

Dave Hargis, Microsoft Access MVP

> I wish to update the MedRecord in Table IntakeInfo with
> thePatientMedicalRecord# from the Table PatientInfo. PatientID is the pk in
[quoted text clipped - 8 lines]
>
> I got nothing. Could someone please help?

Reply to this Message

patti - 24 Apr 2008 21:50 GMT

Thanks for the quick help. I didn't execute the query. i used the datasheet
view to check the query before i ran it. I didn't realize the datasheet view
was useless; wish access would've disabled the datasheet option for update if
it won't show changes.

I ran the query and it does work.

Thanks. I researched the query for a day because datasheet view showed no
changes. Always look at the simplest first, eh? But you gotta to know what is
simplest. So much to learn.

Thanks always to all who help.

> What do you mean by "I got nothing."
>
[quoted text clipped - 16 lines]
> >
> > I got nothing. Could someone please help?

Reply to this Message

Klatuu - 24 Apr 2008 21:54 GMT

Yes, it is confusing. When you look at the datasheet view of an Action
(Update, Delete, Append) query, it doesn't show the changes, it shows which
records will be affected.

Signature

Dave Hargis, Microsoft Access MVP

> Thanks for the quick help. I didn't execute the query. i used the datasheet
> view to check the query before i ran it. I didn't realize the datasheet view
[quoted text clipped - 29 lines]
> > >
> > > I got nothing. Could someone please help?

Reply to this Message

patti - 24 Apr 2008 22:11 GMT

Thanks for that additional info. I was wondering why it was showing anything
at all if it wasn't showing what the changes were going to be. (I would
really like to understand the coding behind access itself.) It is confusing.
I keep bumping into new things everyday! And fortunately you are there to
explain things.

> Yes, it is confusing. When you look at the datasheet view of an Action
> (Update, Delete, Append) query, it doesn't show the changes, it shows which
[quoted text clipped - 33 lines]
> > > >
> > > > I got nothing. Could someone please help?

Reply to this Message

Klatuu - 24 Apr 2008 22:19 GMT

Glad I could help.
There are a lot of people who help on these sites. The site is set up by
Microsoft, but they do not provide any answers. Everyone who answers
questions is doing so for the good of the Access community. We are all like
you. We have regular jobs and we do this because we want to.

Don't worry about what you don't know. I have been programming for over 30
years and in Access for 9 years. Hardly a day goes by that I don't lean
something new.

I have an mdb I call my "Bag of Tricks". Everytime I see something I think
is useful, I copy it and put it in the bag or if I come up with an new idea,
I experiment until I get it like I want it and it goes in the bag.

Just this morning a co worker asked how to do a specific task. It was in
the bag, so I pulled it out and gave him a copy.

Just keep doing it and try to stretch. That is, rather than stay in a
comfortable rut, see if you can find a better way to do it. Or, if you run
into a problem, you know where to go.

Signature

Dave Hargis, Microsoft Access MVP

> Thanks for that additional info. I was wondering why it was showing anything
> at all if it wasn't showing what the changes were going to be. (I would
[quoted text clipped - 39 lines]
> > > > >
> > > > > I got nothing. Could someone please help?

Reply to this Message