How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Steve Herrod Your Own Question

Steve Herrod
Steve Herrod, Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 3288
Experience:  Trained in Microsoft Office from 2000 through to latest version 2010.
65126503
Type Your Microsoft Office Question Here...
Steve Herrod is online now
A new question is answered every 9 seconds

I have a database that has 2 tables, the first table creates

This answer was rated:

I have a database that has 2 tables, the first table creates records in the second table that are unique by a number and a date. The first table has patient id's and demographics, the second table contains visit number, date and blood pressure. The query I want to create needs to pull from the first table and the last visit date of the second table. Is this possible?

Steve Herrod :

Hi, I'll be happy to help with this issue

Steve Herrod :

This should be possible in Microsoft Access if you have that?

Steve Herrod :

What format is the data in currently?

Steve Herrod :

Let me know and I'll be able to advise further

Steve Herrod :

Cheers

Steve Herrod :

Steve

Customer:

I will attach a screen shot of the different tables and relationship.

Steve Herrod :

thanks

Customer:

Is it possible that I email you the project and you can determine what it will take to do what I need.

Steve Herrod :

sure - if you e-mail it [email protected] and put for my attention along with a link to this chat I can work from there

Customer:

clinical data
Full Size Image

Customer:

I can attach the screen shots now and I will email you the whole project

Customer:

relationships
Full Size Image

Customer:

The problem I am having is that I want to create a query that will pull from the demographics table and the third record in the clinical data table. The project has to be limited to two tables for some reason.

Steve Herrod :

ok, I think it should be possible to pull query data from any combination of records

Steve Herrod :

but will work on this when the e-mail comes through

Steve Herrod :

and let you know

Customer:

I just sent email to your attention, I forgot to put this link in so I will send again.

Steve Herrod :

thanks :)

Customer:

I do not know the next step from here please advise.

Steve Herrod :

just working on the file - the query required, what values should it pull from each table?

Customer:

the query needs to pull the initial bp from the demographics table and the bp, bmi from the third record on the clinical data table

Customer:

I have watched numerous training videos about access and could not figure this out. I can only see how to pull from the first record of the clinical data table. I did not see how to specify the third record.

Steve Herrod :

ok, any sorting/criteria needed?

Customer:

not that i know of, i am doing this for my wife i will ask her now

Steve Herrod :

ok

Customer:

no sorting, the only thing she needs is that when the third record is created on any patient the query will pull the bmi and bp from that third record. Is this the best way to make this type of database, In my opinion I do not think so but they are limited to what they can do as far as tables and fields are concerned.

Steve Herrod :

ok so along with the initial bp from demographics table and bp/bmi from clinical data should the patients name also be includeD?

Customer:

yes the pt name and medical record number (mrn)

Customer:

you have helped 532 people since january? wow

Steve Herrod :

lol, guess so

Steve Herrod :

although some of them are repeat customers I guess

Customer:

ok

Steve Herrod :

can you see how Query1 works on this database file?

Customer:

does it have to pull each record or can it be limited to the initial bp and the final bp only

Steve Herrod :

the default is to pull each record

Customer:

is it difficult to change that or does that require a more intensive approach, i know it will incur additional cost, its how much is the question

Steve Herrod :

That would require some more customization on the query I believe - although I just want to be sure I'm understanding it right

Steve Herrod :

when you say 'does it have to pull each record' - do you mean each row in the Access tables?

Customer:

yes i mean each row, i am calling them records because of something i watched in a trainsignal video, when information is put into the demographic table, it is creating a new record or row in the clinical data table and i want the query to pull the third row only, the first two rows are not needed for the query

Steve Herrod :

ok - at the moment if I enter some data into the demographic table, it doesn't follow through to the clinical data - have I missed something?

Customer:

i am not sure, when i input data into the demographics table it did populate to the clinical data table after some time it was not immediate, did i do something wrong

Steve Herrod :

no that's fine, I only did it as a quick test - want to make sure I'm giving you the right advice

Steve Herrod :

the way the query works is only reading certain fields from each table

Steve Herrod :

if you right-click Query1 in Access and choose Design View that should explain it better than I can

Customer:

my wife said she had to compact database to get information to come up in the clinical data table once entered into the demographics table, i did not know that till now

Customer:

ok so it can only read certain fields and not be specified to read rows

Steve Herrod :

yes, can be specified to read whatever fields are required

Customer:

shouldnt it auto populate the fields in the clinical data table, seems to me like i do not have the relationships set up right or something

Steve Herrod :

the query I have put in combines the lastname, firstname fields as well

Customer:

did you see my last question about the relationships

Steve Herrod :

yes - just checking

Customer:

what will it take to read the third row for each person that we add from here on

Steve Herrod :

on the query side do you mean?

Customer:

yes

Steve Herrod :

the query is automatic

Steve Herrod :

and will read the specified rows for any additional person now

Customer:

ok good deal

Steve Herrod :

in terms of autopopulating you would need to create an Access Form and enter details that way

Steve Herrod :

if relationships are correct it should then propogate automatically to ClinicalData

Customer:

ok that is one of the requirements as well, i thought it was strange that we had to enter information into the table, i will start on the form and see how that goes, i may need your help on that as well

Steve Herrod :

no problem

Steve Herrod :

on the query side I think you're all set

Customer:

ok what happens next

Steve Herrod :

how do you mean?

Customer:

the next step in this help process

Steve Herrod :

ah ok - well if you're happy with the service you can rate the answer which credits me. Can add further questions/updates as required on this thread as well without incurring an additional charge

Customer:

when i submit the rating it says it will close chat, but is there a revised file for the database

Steve Herrod :

it will close the chat but you can use the link still to come back and ask additional questions

Steve Herrod :

Did the link I sent earlier not work? That should have been the revised database file

Customer:

ok yes it works fine just making sure, thanks for your help, i will get back to you if i need help with the form

Steve Herrod :

no problem

Steve Herrod :

let me know if you need anything at all

Customer:

have a good day and thanks again

Steve Herrod :

you too

Steve Herrod and 3 other Microsoft Office Specialists are ready to help you