UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Trouble Getting A Single Row, Access 2016    
 
   
RCDAWebmaster
post Jan 16 2020, 11:15 AM
Post#1



Posts: 27
Joined: 27-August 19



I have a table that holds completed steps for jobs.

tblApplicationProcess
_________________
ApplicationProcessID
Step_ID
StepDate
JobLocation_ID
Notes
Applicant_ID

What I'm trying to retrieve is: all the jobs an Applicant is involved with, showing the max of step_ID with StepDate, Notes and JobLocation_ID. This should in theory return 1 row for each job, but it's not.

In the first column, I have the Applicant_ID with criteria of a field on a form
In the second column, I have Max(Step_ID)
In the third column, I have Max(stepDate)

Up to this point, I'm getting one row.
When I add the Notes column, I get all the records for that applicant_ID
when I add the JobLocation_ID column, I get all the records for that applicant_ID

I even tried splitting the single query into multiple queries having the included query have only the first 3 fields and this was expecting only one row per job.

What am I doing wrong?

Attached File  Returned_Rows.jpg ( 36.28K )Number of downloads: 1

I should only get the first row showing that for Applicant_ID 1, JobLocation_ID 1, the max step is 2 which occurred on 1/27/2020 with related notes.

Go to the top of the page
 
theDBguy
post Jan 16 2020, 11:18 AM
Post#2


UA Moderator
Posts: 77,304
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think you do need multiple queries or use subqueries, but how do you know your first query is giving you back the correct information. Are steps performed in order? Is it not possible for a step to be done out of order? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RCDAWebmaster
post Jan 16 2020, 12:23 PM
Post#3



Posts: 27
Joined: 27-August 19



Steps were entered into the table such that they occur in numeric order. Someone can skip a step but they always perform a higher numbered step. So as not to confuse things, I guess I could have relied on the date to show which step was the latest performed, but i didn't. This made adding the next step easier as I could simply populate the combo box with steps that had a higher ID number.

As far as multiple queries, I'm taking a look into it... I finally got it.

It took 3 queries and a lot of thinking...

Attached File  working.jpg ( 396.44K )Number of downloads: 2
Go to the top of the page
 
theDBguy
post Jan 16 2020, 12:26 PM
Post#4


UA Moderator
Posts: 77,304
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RCDAWebmaster
post Jan 16 2020, 12:27 PM
Post#5



Posts: 27
Joined: 27-August 19



thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 09:17 PM