How to keep records straight

[BrianPaul]

I have a database for instructors to use. Two Tables, 1 query, and 1 form.

First table, topicID, autonumber Primary Key, Topicid, number, Subject, text

Second table TopicID autonumber Primary Key, Topic text, memo Topic Remarks

I have a join for the query, A form and subform.

However, I have 3 instructors using it. I want to import there records into
1 Database. How can I do that and still keep the data straight Since they
could have the same Primary Key number, but different topics. Another words
is even possible since the topic text and topic remarks of the 2nd table are
linked to the 1st table.

Thanks. And could I do it with an append query when they brought there
laptops back in.

 

[Tom Wickerath]

Hi Brian,

Danny Lesandrini has some articles available on combining data from
different databases, which you might find helpful:

Merge Records from 2 Identical Database
http://www.amazecreations.com/datafast/ShowArticle.aspx?File=Articles/mergedatabases.htm

http://www.amazecreations.com/datafast/ShowArticle.aspx?File=Articles/mergemdbs.htm

It appears as if you may need to add a table of Instructors. You may also
need to change some of the autonumber values, as appropriate, to import this
data.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Richard]

Hi Brian,

First table, topicID, autonumber Primary Key, Topicid, number, Subject, text
Second table TopicID autonumber Primary Key, Topic text, memo Topic Remarks

Is topicID in the second table a primary key or foreign key? if its not a
foreign key you may have some basic normalization problems. It looks like the
data repeats in the 2nd table.

http://support.microsoft.com/kb/209534

Richard

 

[BrianPaul]

in the second table Topic ID is number Sorry.

For the sake of efficiency I want to use the autonumber as my main primary
key, but what if the destination database already has that autonumber taken?
How can the number be changed while maintaining the links in the linked data?

 

[BrianPaul]

Sorry when writing original post out Topid ID is Number This is where the
link is in the query to the 2nd table. Glad you picked up on that.

 

[John Spencer]

Why do you have three separate databases to do the same thing?

Do you want to merge all the records into one database one time? Or are
you trying to synchronize the data between these three databases?

If the latter you need to redesign your data structure to avoid the use
of autonumber as a primary key and foreign key field. Or you need to use
replication (a complex topic) to synchronize the data. AND that will
change your autonumber fields to use guid type "numbers".

One time merge
-- add a new TEXT field to the tables that you can use as the new
primary key.
-- use an update query to set the value of the new text field to the
value of the primary key plus a designator to identify the computer (A,
B, and C for example).
UPDATE FirstTable SET NewTopicID = TopicID & "A"
UPDATE SecondTable Set NewTopicID = TopicID & "A"
-- Link in the central database to the tables in A and B.
-- Now, you add the records from the table to a central database
INSERT INTO FirstTableCentral (NewTopicID, [Number], Subject, [Text])
SELECT NewTopicID, [Number], Subject, [Text]
FROM FirstTableA
-- Set up relationships between newTopicID fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[BrianPaul]

Good Question, I was talked into using a 2nd table to begin with when I
developed this but here is your answer. All three instructors develop there
own curriculum and they are not all sitting down at the office able to
connect to a server. So from what your saying. Take out the primary key and
assign them a block of numbers to use when they are creating a new topic.
Now I know why some have to create fields and estimate in my example fields
such as TopicID (primary key) Topic Subject, Then have a bunch of them called
topictxt, topicrmk, topictxt1, topicrmk1, and so on in my case up to about
30. Well that would do it but wouldn't be right would it? So I created a 2nd
table so they could put as many topictxt and topicrks as they choose. Thats
what any MVP would reccomend for normialization. Correct? Which threw me
into this problem because I dont have them networked, where the Primary Key
would Autonumber. However, I know I can do an append querry excluding the
TOPICID number and the table will auto populate a New ID. but guess what?,
the 2nd table doesn't update the TOPIC ID to match what the TOPICID is in 1st
table; Thus, the tables are no longer synchronized. I know someone has had
to have the same problem. After viewing much posts, the solution is always
"Well if you are on a network, do this." Well what if you are not on a
network, Well I know I could give them a block of numbers and not set the
primary key to autonumber and hope they stick true to it. Was wondering if
there any other way or code that could be placed where the 2nd table Topic ID
would match up to the new TOPICID in the first table to keep the records
synchronized. I hope I explained my situation cleary. Thanks.

Why do you have three separate databases to do the same thing?

Do you want to merge all the records into one database one time? Or are
you trying to synchronize the data between these three databases?

If the latter you need to redesign your data structure to avoid the use
of autonumber as a primary key and foreign key field. Or you need to use
replication (a complex topic) to synchronize the data. AND that will
change your autonumber fields to use guid type "numbers".

One time merge
-- add a new TEXT field to the tables that you can use as the new
primary key.
-- use an update query to set the value of the new text field to the
value of the primary key plus a designator to identify the computer (A,
B, and C for example).
UPDATE FirstTable SET NewTopicID = TopicID & "A"
UPDATE SecondTable Set NewTopicID = TopicID & "A"
-- Link in the central database to the tables in A and B.
-- Now, you add the records from the table to a central database
INSERT INTO FirstTableCentral (NewTopicID, [Number], Subject, [Text])
SELECT NewTopicID, [Number], Subject, [Text]
FROM FirstTableA
-- Set up relationships between newTopicID fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a database for instructors to use. Two Tables, 1 query, and 1 form.

First table, topicID, autonumber Primary Key, Topicid, number, Subject, text

Second table TopicID autonumber Primary Key, Topic text, memo Topic Remarks

I have a join for the query, A form and subform.

However, I have 3 instructors using it. I want to import there records into
1 Database. How can I do that and still keep the data straight Since they
could have the same Primary Key number, but different topics. Another words
is even possible since the topic text and topic remarks of the 2nd table are
linked to the 1st table.

Thanks. And could I do it with an append query when they brought there
laptops back in.

 

[John Spencer]

Your two table set up is the correct way to do what you are doing.

And your idea of giving each a block of numbers to identify their topics
would work.

You can control data entry by using two forms. Where one form is the
Topics and the second form is the comments on the topics. The secret is
to use the second form as a sub-form on the first form.

If you do that, the records created by the sub-form can automatically
get the foreign key filled in to match the primary key in the main form.
When you add the sub-form to the mainform, you will need to set the
link fields (master and child) to automatically link the correct records
in the comments table to the record in Topics table that is the current
record.

As far as the primary key in the Topics table goes, you could use two
fields as the primary key. The first field would just contain an
identifier for which of the three databases was creating the record and
a second field that gets populated with a number. The number would get
set by an expression like the following that would be populated by code
in the form. In the form's Before insert you would have code like

fldWhichDb = "A"
fldTopicNumber = Nz(DMax("fldTopicNumber","Topics"),0) + 1

OR you could use something like the following to store BOTH values in
one field that you use as the primarykey. fldTopicNumber would be a
text field in this case

fldTopicNumber=
Format(Val(Nz(DMax("fldTopicNumber","Topics"),"0"))+1,"0000000000") & "A"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[BrianPaul]

Thanks, My form and subform or working just like you stated now. However I
have no choice but to put 2 tables on each computer because they dont access
a lan or a wan. Conflict arrises when I transfer to a central database. In
access 2007 I either dont know how to set 2 primary keys in 1 table.

 

[John W. Vinson]

. In
access 2007 I either dont know how to set 2 primary keys in 1 table.

You can't: a table can have only one primary key.

However it can consist of two (or even ten) fields.

Open the Table in design view.
Ctrl-click the two (or more) fields so they're highlighted.
Click the Key icon to make them a joint primary key.

 

[BrianPaul]

Thanks mr. vinson

I have done it by accident in earlier version in access but never had a
reason to do it. could you give me some examples in general why you would
want 2 or more primary keys in access. Anotherwords, what would the purpose
be, thanks.

 

[John W. Vinson]

Thanks mr. vinson

I have done it by accident in earlier version in access but never had a
reason to do it. could you give me some examples in general why you would
want 2 or more primary keys in access. Anotherwords, what would the purpose
be, thanks.

It may seem just a point of jargon but it isn't:

You CANNOT have "two primary keys".

A table can have only ONE Primary Key; it can have other keys, but only one of
them can be primary.

However, that Primary Key can consist of one field - or it can consist of two
fields - or it can consist of more.

A two-field primary key makes sense when you have two attributes, neither of
which is unique in its own right, but they are jointly unique.

A good example is a school enrollment application. You can have a table of
Students, with a primary key StudentID (and other fields such as last name,
first name, grade level, contact information, etc.); and a table of Classes,
with a Primary Key of ClassNo and other fields for class name, etc. Then there
would be a third table to record which students are in which class: Enrollment
let's call it.

This could have fields StudentID (who's enrolled) and ClassID (what they're
enrolled in). Neither one is unique - a student may enroll in more than one
class, and a class will have more than one student, so neither of these fields
- by itself - can be the Primay Key.

However, each student must enroll in one class *only once* - the combination
of StudentID and ClassID may occur only once in the table; and once you know
the student ID and the class ID, you have enough information to uniquely
identify this record - so it makes a good Primary Key.

 

[BrianPaul]

This got real comical and I thought I had it. I Created another key in the
subject which is a text. However in the query where I linked the 2 tables, It
didn't update the subform or query when I changed the subject title.

 

[John W. Vinson]

This got real comical and I thought I had it. I Created another key in the
subject which is a text. However in the query where I linked the 2 tables, It
didn't update the subform or query when I changed the subject title.

Ummm... and why on Earth would you expect it to do so?

What is the Primary Key of your mainform's table?
What is the Primary Key of the subform's table?
How are the two tables related?
Is the "Cascade Updates" checkbox checked?
Which table contains the subject title (or do both, which would be REALLY
REALLY bad design)?

 

[BrianPaul]

Its working great now. However, what is a bummer is I had to create 2 append
queries and run them both. So I set up a perimeter query, Cause My double
click on list box based on the TopicID won't work, Never ran a query like
that before just opened forms based on this example:

Dim stDocName As String
Me.Visible = False
stDocName = "FStudyView"
DoCmd.OpenForm stDocName, acNormal, , "[Topicid] = " & [List92]

Would be nice that it would export 1 single record based on the TopicID on a
query named ExportTopic. However, I have two tables to export and there
linked by subject, which is a text file. To export right now as I am playing
with it. In the ExportTopic query under topic ID i put [Enter topic ID
number] So from the form when you run it you have to put the number in there
to export just 1 record but I have two tables now. When I export both tables
just testing they do link to each other as long as I dont change the subject.
Sorry If I confused you but I do enjoy your help and I am learning.

 

[John W. Vinson]

Its working great now. However, what is a bummer is I had to create 2 append
queries and run them both. So I set up a perimeter query, Cause My double
click on list box based on the TopicID won't work, Never ran a query like
that before just opened forms based on this example:

Dim stDocName As String
Me.Visible = False
stDocName = "FStudyView"
DoCmd.OpenForm stDocName, acNormal, , "[Topicid] = " & [List92]

Would be nice that it would export 1 single record based on the TopicID on a
query named ExportTopic. However, I have two tables to export and there
linked by subject, which is a text file. To export right now as I am playing
with it. In the ExportTopic query under topic ID i put [Enter topic ID
number] So from the form when you run it you have to put the number in there
to export just 1 record but I have two tables now. When I export both tables
just testing they do link to each other as long as I dont change the subject.
Sorry If I confused you but I do enjoy your help and I am learning.

I'd be glad to help if I could, but I cannot because you have not answered my
questions. Good luck.