• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

VBA select data from Multiple columns using SQL

How would I structure a VBA SQL statement (MS Access 2010) to get data from multiple columns? For instance, I have a table that has:
ID,
PickupCustName,
DeliverCustName,
BillCustName,
AuthorizedByName
...

I would like to be able to select distinct and order alphabetically all the names from data in all four columns into one result set so I can put them all in one column of a combo box on my form.
0
Rich
Asked:
Rich
  • 4
  • 4
1 Solution
 
IrogSintaCommented:
Is there a reason you didn't want to have all 4 columns show in your combo box instead?  Did you mean you want to concatenate the 4 fields into one with something like this:
[PickupCustName] & ' ' + [DeliverCustName] & ' ' + [BillCustName] & ' ' + [AuthorizedByName]
0
 
RichAuthor Commented:
I am using the combo box to find any occurrence of the name in one of four fields on a continuous form, so I want all possible names in the combo box, listed together in one column.
0
 
IrogSintaCommented:
If you're using a routIne in vb code to do this, then you can just do the concatenation within your proceedure rather than a combo box. Either way, the sample I gave you earlier should work.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RichAuthor Commented:
I am not following. I have a combo box that has the Data Source set to
SELECT DISTINCT qryBookingsSearch.PickupName
FROM qryBookingsSearch
ORDER BY qryBookingsSearch.PickupName;

Open in new window

I am trying to fill this combo box with the PickupName indicated in the SQL and also the names from the other 3 columns. I am not sure how Concatenating them will work, as I want them all in the list.
0
 
IrogSintaCommented:
Maybe I'm not understanding how you intend to use the combobox to find any occurrence of the name in one of four fields on a continuous form.  Can you explain this process?  Also can you post the SQL code for your qryBookingsSearch query?
0
 
RichAuthor Commented:
The qryBookingSearch is as follows:
SELECT Booking.BOLNumber, Booking.BOLDate, Booking.PickupName, Booking.PickupAddress1, Booking.PickupAddress2, Booking.PickupCity, Booking.PickupState, Booking.PickupZipCode, Booking.PickupTelephone, Booking.PickupContact, Booking.PickupDate, Booking.DeliverName, Booking.DeliverAddress1, Booking.DeliverAddress2, Booking.DeliverCity, Booking.DeliverState, Booking.DeliverZipCode, Booking.DeliverTelephone, Booking.DeliverContact, Booking.DeliverDate, Booking.BillCustomer, Booking.BillAddress1, Booking.BillAddress2, Booking.BillCity, Booking.BillState, Booking.BillZipCode, Booking.BillContact, Booking.BillTelephone, Booking.BillMethod, Booking.Instructions, Booking.AuthorizedBy, Booking.Insurance, Booking.DeclaredValue, Booking.InsuranceLimited
FROM Booking
ORDER BY Booking.BOLNumber DESC;

Open in new window


I would like to fill the combo box with all the names (DISTINCT for no repeats) from the following fields: PickupName, DeliverName, BillingName, AuthorizedBy.
So if the Booking table has names like this
PickupName, DeliverName, BillingName, AuthorizedName
record1: Bob, Bill, John, Tom
record2: Gus, Fred, Frank, John

I would like to return the values:
Bob
Bill
John
Tom
Gus
Fred
Frank

with which I would see it the combo box.
I hope this makes sense...
0
 
IrogSintaCommented:
Well I don't see BillingName or AuthorizedName in your qryBookingSearch but assuming you intend to add them, here's the query for your combo box:
SELECT PickupName FROM qryBookingsSearch UNION SELECT DeliverName FROM qryBookingsSearch UNION SELECT BillingNameFROM qryBookingsSearch UNION SELECT AuthorizedName FROM qryBookingsSearch 
ORDER BY PickupName

Open in new window

You don't need the DISTINCT keyword when using a UNION since there will be no duplicates unless you use UNION ALL.
0
 
RichAuthor Commented:
The Union Statement! Thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now