UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extracting Data From Field That Affects A Foreign Key, Access 2010    
 
   
AlanAnderson
post Nov 11 2017, 10:13 AM
Post#1



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi



Hi All,

I’m in the (laborious) process of creating a normalized database from an incredibly bad, but large, flat file.

I have got most of it done but I have a problem still with two of my new tables.

1. tblePhoneNumbers (This is the foreign key in my CustomerTable)
2 PhoneID Autonumber
3. PhoneNumber Text


The data in the PhoneNumber field can consist of many different phone numbers. Most often they are separated by _ / _ (a space then / then a space.
Sometimes only one number without the _/_ but can be as many as 15.

I'm just listing four records below
QUOTE
• 01 307 331 / 0888 307 331 / 01 705 411 / 0999 016 692
• 01 310 056 / 0999 511 423
• 0111 202 621 / 0111 204 521 / 0997 362 653
• 01 310 226 / 0888 737 836 / 0991 873 358 / 01 310 097 / 01 310 224

Phone numbers are either eight or ten characters in length and will always start with 0 (Zero)

They could begin with 01 (Eight digit numbers)
0111 (9 digit)
088 (9 digit)
099 (9 digit)

I need a way of : A Identifying the various numbers and separating them into individual records whilst : B Not losing their link to the individual customers in the Customer file

2. tblEMails

Pretty much the same problem as with the phones so once I know what to do with the phones I can solve this one

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
orange999
post Nov 11 2017, 11:20 AM
Post#2



Posts: 1,710
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Alan,

For clarity could you show us a few of the related Customer records?

--------------------
Good luck with your project!
Go to the top of the page
 
AlanAnderson
post Nov 11 2017, 11:40 AM
Post#3



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


Hi,

Its pretty unhelpful I think


CustomerID (Autonumber)
CustomerName (Text) eg. Dr Jim Sawbones
CustomerAddressID FK 576
CustomerEMailID FK 7
CustomerPhoneID FK 46
CustomerWbSiteID FK 124

The problem is that the record "46" relating to this customer could have multiple phone numbers which I first need to extract and secondly need to automatically add them to the table but linked bank to this customer

I suspect I will need a "junction table" so that I can have multiple keys for phones.

Regards

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
projecttoday
post Nov 11 2017, 12:00 PM
Post#4


UtterAccess VIP
Posts: 8,616
Joined: 10-February 04
From: South Charleston, WV


What's wrong with the Split function?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
AlanAnderson
post Nov 11 2017, 12:38 PM
Post#5



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


Hi,

I'm sorry I don't understand the question

Hiow would split help me in this case.

Sorry if I'm being dense

Regards

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
projecttoday
post Nov 11 2017, 12:48 PM
Post#6


UtterAccess VIP
Posts: 8,616
Joined: 10-February 04
From: South Charleston, WV


By separating the various numbers.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
orange999
post Nov 11 2017, 12:50 PM
Post#7



Posts: 1,710
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I agree with Robert (projecttoday) --split was my first thought.

I don't see how you are relating Dr Sawbones by the various phone number formats you showed earlier.

Split at the "/" will certainly get individual phone numbers from the string of phone numbers.
But, perhaps you could show us how Customers and Phones relate with a specific example.
Or show us your tables and relationships.

--------------------
Good luck with your project!
Go to the top of the page
 
AlanAnderson
post Nov 12 2017, 03:15 AM
Post#8



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi



SORRY for the delay. We have had no power or internet for nearly a full day now.

Hi All,

Currently if I call up Dr Sawbones on a form one would see the Autonumber and his name iin the usual way.

His EMail would look like this
QUOTE
drjim@greathospita.com,jimsawbones@gmail.com


His phone number field would like this :
QUOTE
01 307 331 / 0888 307 331 / 01 705 411 / 0999 016 692 / 0999 199 601


I would like both the email field and the phone field to be listboxes or a subform containing a datasheet displaying all the numbers / emails relevant to him.


Thanks

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
AlanAnderson
post Nov 12 2017, 03:15 AM
Post#9



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


SORRY for the delay. We have had no power or internet for nearly a full day now.

Hi All,

Currently if I call up Dr Sawbones on a form one would see the Autonumber and his name iin the usual way.

His EMail would look like this
QUOTE
drjim@greathospita.com,jimsawbones@gmail.com


His phone number field would like this :
QUOTE
01 307 331 / 0888 307 331 / 01 705 411 / 0999 016 692 / 0999 199 601


I would like both the email field and the phone field to be listboxes or a subform containing a datasheet displaying all the numbers / emails relevant to him.


Thanks

Alan

I'm enclosing a mockup of more or less what I want to do EXCEPT I want only one phone number per line

This post has been edited by AlanAnderson: Nov 12 2017, 04:07 AM
Attached File(s)
Attached File  Form.PNG ( 11.82K )Number of downloads: 1
 

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
projecttoday
post Nov 12 2017, 03:33 AM
Post#10


UtterAccess VIP
Posts: 8,616
Joined: 10-February 04
From: South Charleston, WV


Should be no problem. Loop through the table and SPLIT that field and write out records from the results of the SPLIT.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
AlanAnderson
post Nov 12 2017, 04:13 AM
Post#11



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


Hi Robert,

Thanks for the ongoing interest.

I think I can manage ok with the SPLIT.
However, what I am not at all sure of is how I can automatically link all the extra phone records back to the customer.

For example Dr Jim currently has one phone record. (Say FK 20) In that record are say three different phone numbers (Say 01 987 432 and 01 986 774 and 0999 983 213)
The two new numbers resulting from the split will not be linked to him

I need to know how to do an automatic process that will address this.

Thanks

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
GroverParkGeorge
post Nov 12 2017, 09:26 AM
Post#12


UA Admin
Posts: 31,019
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

You'll need to do this in VBA because of the need to use Split() to segregate the various phone numbers.

In order assign each newly separated phone number to a record in the new table, and include the appropriate foreign key, you will need to use a variable to hold the relevant Foreign key, and include that in the insert statement which appends the different phone numbers to the new table.
This post has been edited by GroverParkGeorge: Nov 12 2017, 09:42 AM

--------------------
Go to the top of the page
 
projecttoday
post Nov 12 2017, 10:34 AM
Post#13


UtterAccess VIP
Posts: 8,616
Joined: 10-February 04
From: South Charleston, WV


Yes. You can do a recordset for the reading of the current table and a Currentdb.Execute for the output using an INSERT. The customer id field is simply the customer id field in the recordset. You include it as a column in all the INSERTed records.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
AlanAnderson
post Nov 12 2017, 02:24 PM
Post#14



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi



Thanks Guys,

I will give that a try and let you know how it goes

Thanks

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
AlanAnderson
post Nov 13 2017, 03:10 AM
Post#15



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


Hi All,

I'm getting there. Its a long process but I am winning.

Thanks for all the help

Regards

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
tina t
post Nov 13 2017, 01:04 PM
Post#16



Posts: 5,184
Joined: 11-November 10
From: SoCal, USA


QUOTE
I suspect I will need a "junction table" so that I can have multiple keys for phones.

Alan, i'm wondering why you need a many-to-many relationship between phone numbers and customers. i can only see a need for that if both the following statements are true:

1) one phone number may belong to many customers.
2) it's necessary, in your business process, to know that any specific phone number belongs to these specific customers.

if both are true, then yes, a many-to-many relationship with a junction table is necessary. but if the true relationship is:
one customer may have many phone numbers, and each phone number may belong to one customer
then you don't need a junction table. all you need is the pk of the customer record stored in each related phone number record. and in that case, you would get rid of the "phone foreign key" field in the customer table, as it does nothing to support the real-world relationship.

the above also applies to the email addresses and, i would think, to the physical addresses and the websites. you would know that best, of course, as it depends on the real-world relationships between the entities.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
AlanAnderson
post Nov 14 2017, 06:33 AM
Post#17



Posts: 1,307
Joined: 19-October 12
From: Blantyre, Malawi


Hi Tina,

Long t6ime no chat. Good to hear from you.

You, as usual, are total correct,

I will fix it

Thank you

Regards

Alan

--------------------
Kind Regards,

Alan


An intelligent person is never afraid or ashamed to find errors in his understanding of things.
Bryant H. McGill
Go to the top of the page
 
tina t
post Nov 14 2017, 12:20 PM
Post#18



Posts: 5,184
Joined: 11-November 10
From: SoCal, USA


hello Alan, you're welcome, glad to help. and you're right, it's been a long time - it's good to see you too! :) tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th November 2017 - 07:58 AM