cbo box sql help req'd

[Hugh self taught]

Hi All,

I have the following sql in a cbo box. It gives me the male's name but I
can't get the syntax right to give the females name.

The male & female in tblPtsCouples is the PK in tblPtsCompetitors.

SELECT tblPtsCouples.PtsCpl_Idx, tblPtsCouples.MaleCpl,
tblPtsCouples.FemaleCpl, tblPtsCompetitors.First_Name+' '+
tblPtsCompetitors.Surname AS Male
FROM tblPtsCouples INNER JOIN tblPtsCompetitors ON tblPtsCouples.MaleCpl =
tblPtsCompetitors.PtsComp_Idx
ORDER BY tblPtsCouples.MaleCpl;

In this cbo I need to see the male & the female as the male may have more
than one partner. Any help getting that sorted will be appreciated.

 

[Jeff Boyce]

"How" depends on "what". What data structure are you using?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Hugh self taught]

Hi Joyce,

Table tblPtsCompetitors has fields PtsComp_Idx as PK, First_Name & Surname
as text fields along with other fields of pertinent data.
Table tblPtsCouples only has a PK & number fields MaleCpl & FemaleCpl.
As mentioned above the MaleCpl value is the PK of tblPtsCompetitors & the
FemaleCpl value is the PK of tblPtsCompetitors.

My need is to have my combo box drop down show the male name in one column &
the female name in the next column as I need to choose the correct couple
combination. If it was only the male or female column I could do it but the
combination of both in one row is besting me.

Thanks in advance for any assistance you can give on this.

 

[Jeff Boyce]

It sounds as though your "need" is what is hanging you up...

In Access, a combobox doesn't allow selection of items from more than one
column -- the combobox is a list of available rows, so you are only picking
one.

What about the idea of using a pair of comboboxes, one with Males and one
with Females? When you pick one from each, you have your couple...

Or am I still not understanding...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Hugh self taught]

Hi Jeff,

The couples already exist but the male partner may exist with another female
in the database. Therefore when I use the drop down I need to see the male
name & the female name so I know I'm selecting the correct partnership to
capture results for. The couples table only has 3 fields a PK, a MaleCpl (the
PK of tblCompetitors), a FemaleCpl (the PK of tblCompetitors). The First_Name
& Surname are text fields in the tblCompetitors.

I need my cbo Sql to list First_Name+' '+Surname for the malecpl & for the
FemaleCpl so that I see Hugh Whatever & Her Whoever as a line of data
(Column(1) & Column(2) both being visible)

Does that make sense? Can you help with that coz I get stuck trying to get
the FemaleCpl to show the First_Name+' '+Surname but I get the MaleCpl to
show fine.

 

[Marshall Barton]

The couples already exist but the male partner may exist with another female
in the database. Therefore when I use the drop down I need to see the male
name & the female name so I know I'm selecting the correct partnership to
capture results for. The couples table only has 3 fields a PK, a MaleCpl (the
PK of tblCompetitors), a FemaleCpl (the PK of tblCompetitors). The First_Name
& Surname are text fields in the tblCompetitors.

I need my cbo Sql to list First_Name+' '+Surname for the malecpl & for the
FemaleCpl so that I see Hugh Whatever & Her Whoever as a line of data
(Column(1) & Column(2) both being visible)

Does that make sense? Can you help with that coz I get stuck trying to get
the FemaleCpl to show the First_Name+' '+Surname but I get the MaleCpl to
show fine.

Maybe Jeff is tied up today? Anyway, try something like:

SELECT tblPtsCouples.PtsCpl_Idx,
tblPtsCouples.MaleCpl,
tblPtsCouples.FemaleCpl,
tblPtsCompetitors.First_Name+' '+
tblPtsCompetitors.Surname AS Male,
tblPtsCompetitors1.First_Name+' '+
tblPtsCompetitors.Surname AS Female

FROM (tblPtsCouples
INNER JOIN tblPtsCompetitors
ON tblPtsCouples.MaleCpl =
tblPtsCompetitors.PtsComp_Idx)
INNER JOIN tblPtsCompetitors AS tblPtsCompetitors1
ON tblPtsCouples.FemaleCpl =
tblPtsCompetitors1.PtsComp_Idx

ORDER BY tblPtsCouples.MaleCpl

 

[Jeff Boyce]

Jeff was out of office, disconnected from his technology, suffering
electronic withdrawals...

Skippy

 

[Hugh self taught]

Hi Marshall,

That works great!! Thanks a stack. (I've also been out of electronic comms
for a bit)

 

[Hugh self taught]

Hi Jeff,

Marshall's code works great. Thanks for your efforts.