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
> New Table With Old Records, Access 2013    
 
   
vanzie
post May 21 2020, 02:27 AM
Post#1



Posts: 32
Joined: 29-January 19



Hi everyone

I was wondering if someone can help me out with a little mind twister.

I have a table where I store information about people like their names, ID numbers, DOB's, etc. One specific action I want to execute is if I get a different residential address of the person, I want to keep the previous address that was submitted and add the new address. Should I then have to find the person, I can use the addresses I have from old to new.

So person 1 stays at 123 ABC Street in DEC City and the new information I receive shows he stays at 987 XYZ Street I want to store the old address in a separate table and save the new address I received as their current.

Is there a way I can go about this?
Go to the top of the page
 
nvogel
post May 21 2020, 03:55 AM
Post#2



Posts: 1,121
Joined: 26-January 14
From: London, UK


I suggest you don't create a new table just for the old addresses. Create a new table for all the addresses. That table can include the person identifier and the date from which the address applies - or maybe just an attribute that tells you which is the current address. Then you can remove the address attributes from your original person table.
Go to the top of the page
 
jleach
post May 21 2020, 05:36 AM
Post#3


UtterAccess Administrator
Posts: 10,573
Joined: 7-December 09
From: St. Augustine, FL


I agree, that's the normalized way to do it. A bit more work to pull up the current address, but the data structure is sound and useful for exactly these scenarios (probably not applicable to addresses, but when applied to other things, such as a billing rate or similar, you can query point-in-time values, e.g., on 2020-01-15, the rate was $90/hr, so use that in this context even tho the current rate since 2020-02-15 is $120)

Cheers,

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
GroverParkGeorge
post May 21 2020, 07:00 AM
Post#4


UA Admin
Posts: 37,487
Joined: 20-June 02
From: Newcastle, WA


One more consideration.

When you do this, you will also change the interface through which you manage the people and their addresses. It will need to be a main form bound to the people table and a subform in that main form bound to the addresses table.

I would also give consideration to other possible data you may be collecting for email and phone numbers. These should also be handled the same way.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
vanzie
post May 21 2020, 07:42 AM
Post#5



Posts: 32
Joined: 29-January 19



That's why this is a little challenging for me. I was thinking in the same direction to have a table for addresses that correspond with the related person but wanted to ask and see if there is another simpler way to do this. Just was not sure how to connect the dots with the previous and current address but I guess it would not matter really for if I need to find that person I can go to the addresses provided whether they are current or previous addresses
Go to the top of the page
 
Jeff B.
post May 21 2020, 07:43 AM
Post#6


UtterAccess VIP
Posts: 10,489
Joined: 30-April 10
From: Pacific NorthWet


Further backup to posted responses (… or pig-piling on, if you prefer …)

"Moving" data from one table to another to show a change of status (e.g., no longer current, now current, …) is probably more like how you'd have to handle this situation … if you were limited to using a spreadsheet. Access is a relational database, and as others have pointed out, you can do this with a single table, adding one/more flag(s) to indicate the status change/date of status change/etc. The features/functions in Access are optimized to work best with well-normalized data, which multiple tables are not.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post May 21 2020, 08:04 AM
Post#7


UA Admin
Posts: 37,487
Joined: 20-June 02
From: Newcastle, WA


As I mentioned, you handle this one-to-many relationship via a main form/sub form interface design.

Here's a Relationship Diagram of PART of one database based on Individuals.

Attached File  Peopleonetomany.png ( 24.1K )Number of downloads: 6


Note that there is a one-to-many relationship between Individuals (people) and email addresses because most people do not share an email. There is, on the other hand, a many-to-many relationship between people and addresses because one or more people reside at one address and one person may reside at different addresses at different times (home and vacation cabin, for example). In this case, the decision was made to handle people and phones as many-to-many even though many, if not most, people now have a smart phone instead of a landline. Still, there are many families which do have both a "family" landline and individual smart phones.

The interface that handles this design looks like this.

Attached File  peoplemainsub.png ( 39.05K )Number of downloads: 6


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 01:13 PM