sql SELECT statement returns PK, I want second column... (1 Viewer)

[markzaal]

access 2010

Hi all,

I have a table (POBListT) with 3 fields: ID (PK, autonumber), FullName, Position
I have a form (POBDiveF) bound to another table (POBDiveT, 1 date field (DPRDate), 10 name fields (DiverName1-DiverName10) that selects people from this first table with a combo box. It shows only FullName and Position in the dropdown and than saves just the FullName in this field. Like it should. This way I fill my form POBDiveF with 10 people (only 1 record with 10 people per day), selected from the POB-List.
I now use an SQL UNION SELECT statement to to create a query that I can use to make a combobox dropdown list that shows only the 10 divers that were selected on the POBDiveF form.
It all works except for 1 thing: in stead of the FullNames (second column in POBListT) it returns the ID number.
How can I get it to return the FullName value?
Below the SQL statement I used



SELECT POBDiveT.Divername1, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername2, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername3, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername4, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername5, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername6, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername7, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername8, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername9, [DPRDate]
FROM POBDiveT

UNION SELECT POBDiveT.Divername10, [DPRDate]
FROM POBDiveT;

 

[John Big Booty]

Having a table with a structure {10 name fields (DiverName1-DiverName10)} would on the face of things seem to be rather de-normalised.

This table should only hold one Diver name per record and be shown in a continuous sub form that is limited to ten records.

 

[John Big Booty]

Fixing your table structure will make your current task a whole lot more straight forward.

 

[markzaal]

Hi John,

I know that is what it should normally look like. The reason I made it like I did, was because there can only be 1 list per day. My way, I can use the date as PK and keep all names together in 1 record. Is there no workaround to my problem above using SQL?
Another thing is that I want to be able to copy-past the last record in case that day has the same people as the day before...

 

[John Big Booty]

If your table structure is properly Normalised, your List of divers will be linked to to the Trip via the TripID which will be the Primary Key (PK) in the BoatTable and the foreign Key (FK) in the DiversTable in that way you can isolate which divers where on any given trip/boat. Check this link for how to correctly set up a Form/SubForm set. There really is no excuse for de-normalising your data structure, as you are starting to find out it will only cause your grief.

 

[boblarson]

To answer your original question - It would appear that you are using a lookup at table level which, when used with a query like you are trying to use, brings back the ID because that is what is stored in the table. To bring back the name you would need to include the names table and link to it for each of your query parts in your union query.

Read more about lookups at table level, and why they are not good to use, here:
http://www.mvps.org/access/lookupfields.htm