Querying A Table With Multiple Entries in One Field

[Jaq]

Hi,

I'm very new to Access so forgive me if this is a really simple question!

I'm trying to write a query that produces a database of customers and their contact details. The customer reference, name and address is held in one table and the contact details are held in another. The problem I am encountering is that the contact details are stored in one field called CONVAL and could either be email addresses, telephone numbers or both.

The format of the query I have designed is below (sorry I'm not allowed by work to show the database in any way):





And it returns the following output:



Is there a way to produce the output so that email address is one column and telephone number is another?

Thank you.

[CJ_London]

your conval table needs an additional column - called say conType. This would contain a value to indicated type - say 1=telephone, 2=email, 3=skype etc.

then you query would be

Telephone: iif(conType=1,conval,"")

Email: iif(conType=2,conval,"")

Or you could use a crosstab query

[Jaq]

Hi Ajax,

Thanks for your reply!

I've added a new field into the query and it's now as below:



And the output looks like this:



The information is now in the correct columns but shows as 2 separate rows for each customer. Is there a way to produce the information on 1 row rather than 2 so that their email address and phone number is shown on the same line?

Thanks and sorry to ask such basic questions!

[orange]

Or you could reorganize the contact table

CustRef
ContactFName
ContactLName
ContactAddrLine1
ContactAddrLine2
ContactAddrCity
ContactAddrStateProv
ContactPhone
ContactEmail
ContactSkype
ContactFaceBook
....

1 fact 1 field

Be cautious with this sort of thing

..a way to produce the information on 1 row rather than 2 so that their email address and phone number is shown on the same line?...

data storage/structure is one thing
data presentation is another

[Jaq]

Hiya,

Because the data feeds into our main live system I am unable to update the table layout - they are hard coded by our software supplier. Sadly I can only query the information contained in the tables themselves.

Jaq

[CJ_London]

Is there a way to produce the information on 1 row rather than 2 so that their email address and phone number is shown on the same line?

you can use a crosstab query based on table2 and join that to table 1 in place of table2

for the crosstab (assuming cusRef is the name of your linking field in table2)

cusRef - row heading
conMethod - column heading - note you might want to use a formula instead - iif(conMethod=1,"Phone","Email")
conVal - value (use first)


then link this query to table1 instead of table2