Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 9th, 2007, 09:38 AM
Registered User
 
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding Data to aTbl from another Tbl

I have 2 tables Customer1 and Customer2 both table structures are the same except Customer1 has outdated data, I need to add additional customers and data that is unique to each customer from Customer2 to Customer1, each customer has a customer number which is also the primary key, any help would be greatly appreciated.

 
Old April 10th, 2007, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How about adding a field to Customer1 called Outdated, and make it a Yes/No check box, default value = No. Then filter your forms and reports to run only where Outdated = No. This way you can keep all your data in one table. This is standard.

Moving data to another table is usually only done to archive the data - move it out of your database. If you are already keeping it in one database, then just use the Outdated switch. Much simpler design (one table), no coding (moving data between tables, adding a customer back to active table), just query parameters (WHERE [Outdated] = No).

Did that help?


mmcdonal
 
Old April 10th, 2007, 06:31 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

fwebb,

mmcdonal is bang on right here, thats definately the best way to do it.
It never makes sense to have the "same" data in two tables. Always try and keep it in one.

This not only makes filtering easier, but also operations such as archiving etc.
For example, you could produce a very quick update query to mark all cases that are over 6 months old as "Outdated".

Some people prefer to KIS (Keep it Simple), but I also like to KIL (Keep it Logical) =)

Regards,
Rob

 
Old April 10th, 2007, 08:21 AM
Registered User
 
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the responses, I probably didn’t explain my issue well enough, the data in Customer1 contains standard customer info (Customer#, Name, Address, Etc.) the data in Customer2 contains the same customer information and table structure except it has additional customers and the FFL_Info field is populated with data that is not in Customer1, so what I wanted to do was add the additional customers and populate corresponding FFL_Info field in Customer1 from Customer2. I was able to do what I wanted by creating a new table named Customer3, created an append query to suck the data from Customer2 and insert it into Customer3 which it did populate, then renamed Customer1 to something else and renamed Customer3 to Customer1 and it seems to have worked. I know this was a very clumsy way to do this and I am sure there is a more elegant way to accomplish this and would appreciate any help with doing so as I will run into this again. Sorry for the long post.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Change tbl permissions using VBA feets Access VBA 1 October 12th, 2007 01:19 AM
Use a frm based on a qry to update a tbl Brucifier Access 1 August 8th, 2006 10:06 PM
get latest record frm transaction tbl jyopallavi SQL Server 2000 1 June 9th, 2005 08:11 AM
csv to tbl - best method for updating and removing Stuart Stalker SQL Server DTS 9 July 1st, 2004 03:25 PM
Retrieve and display data without tbl messed up lxu Access 0 March 21st, 2004 11:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.