If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicate data
I am trying to maintain different state for filenames, so i have a table
with the list of filenames, then in the main table i would link the filename field to it. However this looks like it is duplicating the actual filename for each record (i have 100,000's of them so this is bad). I have set up one-to-many relationship and have enforce relationship integrity enabled. How can i prevent the filename being duplicated per record? Main table ID Autonumber (primary key) FileName Text - related to FileName.Name Action Text Filename table Name Text (Primary key) - related to Main.FileName Also, i am using ODBC to actually insert the record in the database like so CHECK(retcode = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (TCHAR*)FileName, sizeof(FileName), &FileNameInd)); "INSERT INTO Main (FileName, Action) VALUES (?, ?)" I thought about adding a ID field to Filename table but that seems overkill considering the name is unique and is the primary key. |
#2
|
|||
|
|||
What you describe as your setup sounds perfectly acceptable to me. What
you're doing by putting the state name in a different table is avoiding the need to change 100,000s of records when you find a typo in a state name. You just change it once in the state name table, and all other records change at the same time. Don't change a thing! g -- Ken Snell MS ACCESS MVP "Rob Green" wrote in message ... I am trying to maintain different state for filenames, so i have a table with the list of filenames, then in the main table i would link the filename field to it. However this looks like it is duplicating the actual filename for each record (i have 100,000's of them so this is bad). I have set up one-to-many relationship and have enforce relationship integrity enabled. How can i prevent the filename being duplicated per record? Main table ID Autonumber (primary key) FileName Text - related to FileName.Name Action Text Filename table Name Text (Primary key) - related to Main.FileName Also, i am using ODBC to actually insert the record in the database like so CHECK(retcode = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (TCHAR*)FileName, sizeof(FileName), &FileNameInd)); "INSERT INTO Main (FileName, Action) VALUES (?, ?)" I thought about adding a ID field to Filename table but that seems overkill considering the name is unique and is the primary key. |
#3
|
|||
|
|||
It is duplicating the data. if i add 1000 entries to the database, then
look at it in a hex viewer, i see 1001 copies of the filename. This is what i am trying to prevent. Could it be that i am using a text field for the primary key in the FileName table? I am not really worried about changing the name due to a type, but more so that i will have a 100 entries for the same filename. However i did try changing an entry in the filename table and got the following error 'This record cannot be deleted or changed because table 'main' includes related records'. Maybe i have the database set up incorrectly? I couldnt find how to export the SQL statement for making the database but here is the main.xsd ?xml version="1.0" encoding="UTF-8"? xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlnsd="urn:schemas-microsoft-comfficedata" xsd:element name="dataroot" xsd:complexType xsd:choice maxOccurs="unbounded" xsd:element ref="main"/ /xsd:choice /xsd:complexType /xsd:element xsd:element name="main" xsd:annotation xsd:appinfo od:index index-name="PrimaryKey" index-key="ID " primary="yes" unique="yes" clustered="no"/ od:index index-name="Action" index-key="Action " primary="no" unique="no" clustered="no"/ od:index index-name="Actionmain" index-key="Action " primary="no" unique="no" clustered="no"/ od:index index-name="ID" index-key="ID " primary="no" unique="no" clustered="no"/ od:index index-name="LongFileNamemain" index-key="LongName " primary="no" unique="no" clustered="no"/ od:index index-name="LongName" index-key="LongName " primary="no" unique="no" clustered="no"/ /xsd:appinfo /xsd:annotation xsd:complexType xsd:sequence xsd:element name="ID" od:jetType="autonumber" od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" xsd:simpleType xsd:restriction base="xsd:integer"/ /xsd:simpleType /xsd:element xsd:element name="LongName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar" xsd:simpleType xsd:restriction base="xsd:string" xsd:maxLength value="255"/ /xsd:restriction /xsd:simpleType /xsd:element /xsd:sequence /xsd:complexType /xsd:element /xsd:schema and here is the filename table ?xml version="1.0" encoding="UTF-8"? xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlnsd="urn:schemas-microsoft-comfficedata" xsd:element name="dataroot" xsd:complexType xsd:choice maxOccurs="unbounded" xsd:element ref="LongFileName"/ /xsd:choice /xsd:complexType /xsd:element xsd:element name="LongFileName" xsd:annotation xsd:appinfo od:index index-name="PrimaryKey" index-key="Name " primary="yes" unique="yes" clustered="no"/ /xsd:appinfo /xsd:annotation xsd:complexType xsd:sequence xsd:element name="Name" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar" xsd:simpleType xsd:restriction base="xsd:string" xsd:maxLength value="255"/ /xsd:restriction /xsd:simpleType /xsd:element /xsd:sequence /xsd:complexType /xsd:element /xsd:schema "Ken Snell [MVP]" wrote in message ... What you describe as your setup sounds perfectly acceptable to me. What you're doing by putting the state name in a different table is avoiding the need to change 100,000s of records when you find a typo in a state name. You just change it once in the state name table, and all other records change at the same time. Don't change a thing! g -- Ken Snell MS ACCESS MVP "Rob Green" wrote in message ... I am trying to maintain different state for filenames, so i have a table with the list of filenames, then in the main table i would link the filename field to it. However this looks like it is duplicating the actual filename for each record (i have 100,000's of them so this is bad). I have set up one-to-many relationship and have enforce relationship integrity enabled. How can i prevent the filename being duplicated per record? Main table ID Autonumber (primary key) FileName Text - related to FileName.Name Action Text Filename table Name Text (Primary key) - related to Main.FileName Also, i am using ODBC to actually insert the record in the database like so CHECK(retcode = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (TCHAR*)FileName, sizeof(FileName), &FileNameInd)); "INSERT INTO Main (FileName, Action) VALUES (?, ?)" I thought about adding a ID field to Filename table but that seems overkill considering the name is unique and is the primary key. |
#4
|
|||
|
|||
Sounds as if you have the filename table set up as a child of the main
table. It should be reversed. Or you don't have Cascade Update checked for the referntial integrity. The filename table should have the file name field be set as a primary key field. It can be a text field; no problem. The main table should have the file name field set with the same data type and text length as the file name field in the filename table. It can be set to Indexed: Yes (Duplicates OK) if you wish. Be sure to not set it to "No Duplicates". Then open the relationship window. If not already there, add the two tables: filename and main. Click on the file name field in the filename table; and drag it to the file name field in the main table. A window will show, and you want to require referntial integrity. The master field should be the file name field of the filename table, and the child field should be the file name field in the main table. Also check the Cascade Update Related Fields box. Click OK in this referential integrity window. You now should see a 1 on the file name field of the filename table; and an infinity symbol on the file name field of the main table. This then means that you can use any file name value from the filename table in the main table, and if you change the name in the filename table then the names will change in the main table with no additional work by you. If you're concerned about storing the file name text in so many records, then add an autonumber field to the file name table, set it as the primary key field, and use that primary key value in the main table in place of the file name text. You'll need to delete the relationship in the Relationship window first, and then be sure to change the type of field in the main table to be a Long Integer field, and then you'll need to reset the relationships. -- Ken Snell MS ACCESS MVP "Rob Green" wrote in message ... It is duplicating the data. if i add 1000 entries to the database, then look at it in a hex viewer, i see 1001 copies of the filename. This is what i am trying to prevent. Could it be that i am using a text field for the primary key in the FileName table? I am not really worried about changing the name due to a type, but more so that i will have a 100 entries for the same filename. However i did try changing an entry in the filename table and got the following error 'This record cannot be deleted or changed because table 'main' includes related records'. Maybe i have the database set up incorrectly? I couldnt find how to export the SQL statement for making the database but here is the main.xsd ?xml version="1.0" encoding="UTF-8"? xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlnsd="urn:schemas-microsoft-comfficedata" xsd:element name="dataroot" xsd:complexType xsd:choice maxOccurs="unbounded" xsd:element ref="main"/ /xsd:choice /xsd:complexType /xsd:element xsd:element name="main" xsd:annotation xsd:appinfo od:index index-name="PrimaryKey" index-key="ID " primary="yes" unique="yes" clustered="no"/ od:index index-name="Action" index-key="Action " primary="no" unique="no" clustered="no"/ od:index index-name="Actionmain" index-key="Action " primary="no" unique="no" clustered="no"/ od:index index-name="ID" index-key="ID " primary="no" unique="no" clustered="no"/ od:index index-name="LongFileNamemain" index-key="LongName " primary="no" unique="no" clustered="no"/ od:index index-name="LongName" index-key="LongName " primary="no" unique="no" clustered="no"/ /xsd:appinfo /xsd:annotation xsd:complexType xsd:sequence xsd:element name="ID" od:jetType="autonumber" od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" xsd:simpleType xsd:restriction base="xsd:integer"/ /xsd:simpleType /xsd:element xsd:element name="LongName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar" xsd:simpleType xsd:restriction base="xsd:string" xsd:maxLength value="255"/ /xsd:restriction /xsd:simpleType /xsd:element /xsd:sequence /xsd:complexType /xsd:element /xsd:schema and here is the filename table ?xml version="1.0" encoding="UTF-8"? xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlnsd="urn:schemas-microsoft-comfficedata" xsd:element name="dataroot" xsd:complexType xsd:choice maxOccurs="unbounded" xsd:element ref="LongFileName"/ /xsd:choice /xsd:complexType /xsd:element xsd:element name="LongFileName" xsd:annotation xsd:appinfo od:index index-name="PrimaryKey" index-key="Name " primary="yes" unique="yes" clustered="no"/ /xsd:appinfo /xsd:annotation xsd:complexType xsd:sequence xsd:element name="Name" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar" xsd:simpleType xsd:restriction base="xsd:string" xsd:maxLength value="255"/ /xsd:restriction /xsd:simpleType /xsd:element /xsd:sequence /xsd:complexType /xsd:element /xsd:schema "Ken Snell [MVP]" wrote in message ... What you describe as your setup sounds perfectly acceptable to me. What you're doing by putting the state name in a different table is avoiding the need to change 100,000s of records when you find a typo in a state name. You just change it once in the state name table, and all other records change at the same time. Don't change a thing! g -- Ken Snell MS ACCESS MVP "Rob Green" wrote in message ... I am trying to maintain different state for filenames, so i have a table with the list of filenames, then in the main table i would link the filename field to it. However this looks like it is duplicating the actual filename for each record (i have 100,000's of them so this is bad). I have set up one-to-many relationship and have enforce relationship integrity enabled. How can i prevent the filename being duplicated per record? Main table ID Autonumber (primary key) FileName Text - related to FileName.Name Action Text Filename table Name Text (Primary key) - related to Main.FileName Also, i am using ODBC to actually insert the record in the database like so CHECK(retcode = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 255, 0, (TCHAR*)FileName, sizeof(FileName), &FileNameInd)); "INSERT INTO Main (FileName, Action) VALUES (?, ?)" I thought about adding a ID field to Filename table but that seems overkill considering the name is unique and is the primary key. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Warn of entry of duplicate data in field on a form | RAO | Using Forms | 2 | November 4th, 2004 01:53 PM |
Export data from OLE object in PowerPoint | Brett Ellingson | Powerpoint | 8 | August 25th, 2004 12:28 AM |
Washing Data - ie, deleting duplicate records in tables | JSquare | Running & Setting Up Queries | 1 | August 23rd, 2004 10:17 PM |
Countif with 2 or more data ranges in same column | Doug | Worksheet Functions | 1 | July 4th, 2004 08:57 AM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |