Question

Locked

How to select one record from a table with duplicate records

By mmunitz ·
I have an Access query in which I need to use a table that has multiple occurrences of a data field I need, and when I join it to my query it causes the entire query to display multiple records.

The table name is tbl_JOINTDATA.
The data fields that are causing duplicate records JOINT_TOUR_END and JOINT_TOUR_START. I only want to retrieve the record if JOINT_TOUR_START IS NOT NULL and JOINT_TOUR_END is NULL. I used the following:
JOINT START: IIf(IsNull([tbl_JOINTDATA.JOINT_TOUR_END]),([tbl_JOINTDATA.JOINT_TOUR_START]))

It displayed the correct JOINT_TOUR_START with the associated NULL JOINT_TOUR_END, but still creates duplicate records.

I need to know how to get that one occurrence of JOINT_TOUR_START when JOINT_TOUR_END is NULL.

Thanks.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

SELECT DISTINCT?

by oldbaritone In reply to How to select one record ...

There wasn't much about your data, but would "SELECT DISTINCT" retrieve what you want?

Good luck.

Collapse -

Confused now....

by Tony Hopkinson In reply to How to select one record ...

Select * tbl_JointData
Where Joint_Tour_Start is not null
and Joint_Tour_End is null.

if you get more than one, then there is more than one...

Get that bit right and then look at any joins...

Use sql, it's what it's for.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums