Create one field from 2 query fields (1 Viewer)

[jamphan]

I have 2 queries that return if the results occur more than once in the table, one for Home Phone and one for Work Phone. These are 2 seperate fields in my table. I would like to combine the results for the phone numbers to make one master field for multiple phone numbers whether its home or work. Is this possible?

 

[georgedwilkinson]

Yes, had you normalized your original design, you would have had a separate telephone table that stored the numbers. Now, you must normalize your design and convert your data to use the new design.

New table - TelephoneType
ID (PK Autonumber)
TelephoneType (Text - contains "cellular", "home", "office", "barnyard", whatever)
Description
FromDate
ToDate
etc.

New table - PartyTelephones
ID (PK Autonumber)
PartyID (FK from whatever table your people/organizations live in)
TelephoneType (FK from above table)
Description
FromDate
ToDate
etc.

This arrangement works well when multiple people don't share the same telephone. It is a good beginning step towards normalizing your design. Please look up normalization.

 

[jamphan]

I am trying to capture merchants who submit multiple/fraudulent phone numbers. I just want to combine the results for my "fraud" phone numbers, work and home together in one table. My question is how can I moved the data from 2 fields into one field.

 

[jamphan]

I should say I want to combine the data into one field not table.

 

[georgedwilkinson]

Ok, got it.

In your underlying query (sql view), concatenate the 2 fields together something like this:
select phone1 & " " & phone2 as Phone, otherfields from mytable;

HTH

 

[jamphan]

Not exactly what I had in mind. I don't want the 2 phone numbers to become one number I just want the 2 numbers to be in the same table field list so I can say # 123 is a fraud number whether it be work or a home number.

 

[georgedwilkinson]

I'll have to defer the answer to my associates, then.

 

[jamphan]

Any help?????

 

[neileg]

Create a select query for each of your telephone fields and then union them together. Make sure you use UNION ALL otherwise duplicates will be eliminated!

As George said, the properly normalised design would have phone numbers in a separate table with a field to identify the phone type.