Sabtu, 07 April 2012

Re: [MS_AccessPros] Major data issues.

 

Crystal 

But the AssociationEIN as the PK is text and the ManagementCoID in the tblManagementCo table is number. I guess that I am confused

 
Jim Wagner
________________________________

________________________________
From: Crystal <strive4peace2008@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, April 7, 2012 12:04 PM
Subject: Re: [MS_AccessPros] Major data issues.


 
Hi Jim,

use the autonumber field that is already there.  Put a unique index on the field that is currently PK if that is a rule for the data.  I often ignore nulls.

Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *

________________________________
From: Jim Wagner <luvmymelody@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, April 7, 2012 12:51 PM
Subject: Re: [MS_AccessPros] Major data issues.

Crystal,

There is already an Auto number field. I do not know what it is for. I contacted the client to ask them what it is used for. I unfortunately gave them my documentation and do not remember what the field is for. 
 
Jim Wagner
________________________________

________________________________
From: Crystal <strive4peace2008@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, April 7, 2012 11:45 AM
Subject: Re: [MS_AccessPros] Major data issues.

 
Hi Jim,

make an autonumber field for the PK.  Use the EIN/TaxID/Whatever as a way to pick them.  Keep that as Text since you do not need to calculate them :)

in related tables, use Long Integer for the FK data type

PK = Primary Key
FK = Foreign Key

Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *

________________________________
From: Jim Wagner <luvmymelody@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, April 7, 2012 11:33 AM
Subject: Re: [MS_AccessPros] Major data issues.

John

I have been looking at the database and trying to set up the tables but I have noticed an issue that you could possibly shed some light on. I have asked this question before about many to many relationships and was thinking if I am going to redesign the tables, I should add the many to many relation to the mix. But I have come upon some issues that I think will make it impossible to fix. 

The client has a primary key in the tblAssociation with the name of AssociationEIN which is the associations tax id. but the problem is that it is a text format with a value shown below which they told me was the tax id. But as I look at the values, there is no way they are tax id's. The client is entering something different. But I was doing some research on many to many and the things I read said that the fields need to be numbers not text. I tried to convert the field to a number but hyphens do not follow a number format it looks like. Is it true that these pk fields need to be numbers to link as a many to many? Your answer will help me to decide to try something else. 
01-0842787

02-149-9999
02-150-9999

Thank You 

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 5, 2012 11:21 PM
Subject: RE: [MS_AccessPros] Major data issues.

 
Jim-

I've seen a discussion on another forum where the PK and relationships on a
table disappeared.  Apparently, JET will drop the relationships and the indexes
if data in the table becomes corrupted.  It's a very rare occurrence, and not
likely to happen on more than one table at a time.  If all the PKs are missing
in your tables, then it's probably not this "bug."

One way to clean it up is to import all the tables – structure only – into a new
database.  Rebuild all the Pkeys and relationships, then link to the old tables
one at a time and run an Append query from the old to the new.  That should drop
duplicates and get rid of unlinked child records.  Note that you'll have to do
this one table at a time working down from the top of your relationship tree.
For example, you'll have to do Customers before Orders.

Good luck!

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Thursday, April 05, 2012 11:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Major data issues.

 
Hello All,

I have just looked at the data tables I had built for a client and I am
horrified. I had the table relations correct and the tables designed with PK's
and everything. But as I look at the design, the PK's are not set and the
related field data in each table is either missing or has a 0. They do not have
the knowledge on what to do in the table design so I must have taken the PK off
at sometime and did not put it back.

Somehow the relations have kept the records on the forms working but I am at a
loss on what should my next move be. They did an audit and the records all match
based on the forms, but the tables are a mess.

Any Suggestions

Jim Wagner

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar