Multiple, unique values into the one row

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

I have two tables...

The first one contains customer name & address details, along with a
unique account identifier. The second one contains all telephone
numbers that we have for each customer, along with a unique identifier
to link it to the customer table. So:

tblCust tblPhone
fldID fldID
fldName fldStatus
fldAddr1 fldNumber
fldAddr2 fldDescription
fldAddr3
fldPostcode

Each customer can have 1 or more entries in the table tblPhone, as
they may have only a Home Phone, a Home & Work phone, a Home & Mobile,
a Home, Mobile & Work etc. What I need to do is create a new table
that will hold the customer details and an entry for each 'phone
number, all in the one record. At the moment, I can have up to 4
records for the one customer, but for the purposes of an outbound
dialler file, I need this to be all on one row.

Possible?

TIA

Duncs
 
m:
I have two tables...

The first one contains customer name & address details, along with
a unique account identifier. The second one contains all
telephone numbers that we have for each customer, along with a
unique identifier to link it to the customer table. So:

tblCust tblPhone
fldID fldID
fldName fldStatus
fldAddr1 fldNumber
fldAddr2 fldDescription
fldAddr3
fldPostcode

Each customer can have 1 or more entries in the table tblPhone, as
they may have only a Home Phone, a Home & Work phone, a Home &
Mobile, a Home, Mobile & Work etc. What I need to do is create a
new table that will hold the customer details and an entry for
each 'phone number, all in the one record. At the moment, I can
have up to 4 records for the one customer, but for the purposes of
an outbound dialler file, I need this to be all on one row.

Possible?

TIA

Duncs
Click to expand...

You do not need a new table _/1, just a Crosstab query based on
tblPhone

Start with a SELECT query, with the fields being your customerID, the
type and the number.

From the toolbar, change to a Crosstab Query.You'll see 2 new rows in
the query design grid, labeled Total: and Crosstab:

in the Totals: row, change number from Group By to First
in the Crosstab: row, select Row Heading under CustomerId,
Column Heading under the type and Value under Number.

View it, it should be what you need.
Then save the query and make a new query joining it to customers.

_/1 making a new table will only cause heartaches because you will
need to synchronize the entries whenever a customer is added ,
deleted, or gets a new number. The query will always have the current
data, so you only need to change it in the one place, not 2.
 
I have two tables...

The first one contains customer name & address details, along with a
unique account identifier. The second one contains all telephone
numbers that we have for each customer, along with a unique identifier
to link it to the customer table. So:

tblCust tblPhone
fldID fldID
fldName fldStatus
fldAddr1 fldNumber
fldAddr2 fldDescription
fldAddr3
fldPostcode

Each customer can have 1 or more entries in the table tblPhone, as
they may have only a Home Phone, a Home & Work phone, a Home & Mobile,
a Home, Mobile & Work etc. What I need to do is create a new table
that will hold the customer details and an entry for each 'phone
number, all in the one record. At the moment, I can have up to 4
records for the one customer, but for the purposes of an outbound
dialler file, I need this to be all on one row.

Possible?
Click to expand...

Yes, with help from a bit of VBA code; see
http://www.mvps.org/access/modules/mdl0004.htm

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
m:









You do not need a new table _/1, just a Crosstab query based on
tblPhone

Start with a SELECT query, with the fields being your customerID, the
type and the number.

From the toolbar, change to a Crosstab Query.You'll see 2 new rows in
the query design grid, labeled Total: and Crosstab:

in the Totals: row, change number from Group By to First
in the Crosstab: row, select Row Heading under CustomerId,
Column Heading under the type and Value under Number.

View it, it should be what you need.
Then save the query and make a new query joining it to customers.

_/1 making a new table will only cause heartaches because you will
need to synchronize the entries whenever a customer is added ,
deleted, or gets a new number. The query will always have the current
data, so you only need to change it in the one place, not 2.- Hide quotedtext -

- Show quoted text -
Click to expand...

Bob,

Works a treat!

Cheers

Duncs
 
Yes, with help from a bit of VBA code; seehttp://www.mvps.org/access/modules/mdl0004.htm

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

John,

Thanks also for your reply. Whilst a great suggestion, I think it may
just be a bit like using a sledgehammer to crack open a nut! It seems
a bit too complex for such a simple task. However, many thanks fo
rtaking the time to read my post & reply.

I'll keep a hold of the link, just in case it comes in useful later.

Cheers

Duncs
 
Back
Top