simple query help

[Theo Brandt]

a simple problem, I think-

I have two tables:

tblWorker
WorkerId
Name

tblContact
WorkId
type (Phone, Mobile)
Prefix (03, 07, 021, etc)
Detail (123 456)


I want the query to generate a table that doesn't generate
more than one line for worker

ie a simple query does this:

Workid Name type Prefix Detail

worker1 name1 mobile 021 123 456
worker1 name1 phone 03 456 789


what I want is:

Workid Name type Prefix Detail type Prefix Detail
worker1 name1 mob 021 123 456 phone 03 456 789

any ideas gratefully accepted!

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane with
samples of how to use.

 

[Gary Walter]

Hi Theo,

In addition to Duane's sage advise,
another method is bring tblContact twice
into your query.

click on "Create Query in Design View"

In the Show Table dialog box,
click on tblWorker,
click Add,
click on tblContact,
click Add,
click on Add again,
and then click Close.

You should now show 1 copy of tblWorker
table and 2 copies of tblContact table
in the query designer.

Right-mouse click one of the tblContact tables
and choose Properties.
In the Alias row, type in
tPhone
then close the Properties dialog box.

Right-mouse click on the other tblContact table
and choose Properties.
In the Alias row, type in
tMobile
then close the Properties dialog box.

Drag and drop tPhone.[Type] down
into a Field row of the grid, and in its
Criteria row, type

Phone

Drag and drop tMobile.[Type] down
into a Field row of the grid, and on its
Criteria row, type

Mobile

I assume you join the tables on [WorkerID]
and [WorkID]?

Click and hold down on left table's
[WorkerID] field
and "drag and drop"
over on tMobile's [WorkID] field.
Right-mouse click on this join line,
choose "Properties", and change to
"Select all of tblWorker..."

Click and hold down on left table's
[WorkerID] field
and "drag and drop"
over on tPhone's [WorkID] field.
Right-mouse click on this join line,
choose "Properties", and change to
"Select all of tblWorker..."

You should now just show our 2 join lines.
If there are any others created by Access,
right-mouse click on them and delete.

Then, just drag and drop all the other fields
down to the grid (for each "type" field you
might want to add an alias for it, i.e., for example,
where you might originally have:

Field: PreFix
Table: tMobile
Sort:
Show: <checked>
Criteria:
Or:

change to:

Field: MobilePreFix: PreFix
Table: tMobile
Sort:
Show: <checked>
Criteria:
Or:

I would think by aliasing this way,
you would not even need to include
"[Type]" in your query.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter