One-table query, listing multiple fields in one listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Memento - 27 Sep 2007 09:43 GMT

Hello guys,

I'm think i just might be trying to do something here that seems to be
difficult, or poorly documented. I am trying to build a query based on one
table (hardware). The table hardware contains fields like manufacturer,
model, type, ipaddress, mapping, location, etc...

I'm using a listbox, and want to be able to view (in one column),
manufacturer, model & type (SELECT hardware.manufacturer & " " &
hardware.model & " - " & hardware.type FROM Hardware)

But i also want the IP-adresses, mapping and location listed in the same
listbox (one column)...

So i would have (for example):

- Intel Core 2 Duo E6600 - CPU : example hardware
- Hewlett Packard LaserJet 2015n - Laserprinter : example hardware
- HPLASER2015 : example mapping
- 188.88.88.90 : example IP
- North Carolina : example location

Is this even possible?

With regards,

Memento

Reply to this Message

John Spencer - 27 Sep 2007 12:58 GMT

You could probably use a UNION query as the source for the listbox

SELECT "A" as ColOrder
, PrimaryKeyField
, hardware.manufacturer & " " & hardware.model & " - " & hardware.type as
DataCol
FROM Hardware
UNION
SELECT "B" as ColOrder
, PrimaryKeyField
, Mapping
FROM Hardware
UNION
SELECT "C" as ColOrder
, PrimaryKeyField
, Location
FROM Hardware
ORDER BY PrimaryKeyField, ColOrder

You could hide the first two columns in the listbox

BUT, I think you would be better off using a subform to display the data.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hello guys,
>
[quoted text clipped - 23 lines]
>
> Memento

Reply to this Message