What should I set for Primary Key

I am trying to create an Account Reconciliation Databse which will compare
two tables. Two tables are Bank and Ledger. Both tables have same fields.
Users will import bank file to bank table and general ledger file to ledger
table. Since both tables haave the same fields, what should I do with
Primary key? My queries are running against both tables, such as Match Query,
UnMatched Query. Suggestions?


Thank you.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

A primary key is something that uniquely identifies a record. You
will have two, one in each table, but they will be the same.

Cheers,
Jason Lepack

On Mar 4, 10:29 am, "accessuser via AccessMonster.com" u28669@uwe
wrote:
I am trying to create an Account Reconciliation Databse which will compare
two tables. Two tables are Bank and Ledger. Both tables have same fields.
Users will import bank file to bank table and general ledger file to ledger
table. Since both tables haave the same fields, what should I do with
Primary key? My queries are running against both tables, such as Match Query,
UnMatched Query. Suggestions?

Thank you.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

you can't link two primary keys, right? only primary key to foreign key,
isn't it right??


thanks

Jason Lepack wrote:
A primary key is something that uniquely identifies a record. You
will have two, one in each table, but they will be the same.

Cheers,
Jason Lepack

On Mar 4, 10:29 am, "accessuser via AccessMonster.com" u28669@uwe
wrote:
I am trying to create an Account Reconciliation Databse which will compare
two tables. Two tables are Bank and Ledger. Both tables have same fields.
[quoted text clipped - 7 lines]
--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

The definition of a Foreign Key is that it is a Primary Key in another
table. Therefore you link the two primary keys and they are both
Primary and Foreign Keys.

On Mar 5, 8:00 am, "accessuser via AccessMonster.com" u28669@uwe
wrote:
you can't link two primary keys, right? only primary key to foreign key,
isn't it right??

thanks

Jason Lepack wrote:
A primary key is something that uniquely identifies a record. You
will have two, one in each table, but they will be the same.

Cheers,
Jason Lepack

On Mar 4, 10:29 am, "accessuser via AccessMonster.com" u28669@uwe
wrote:
I am trying to create an Account Reconciliation Databse which will compare
two tables. Two tables are Bank and Ledger. Both tables have same fields.
[quoted text clipped - 7 lines]
--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

How is a record in one table associated with a record in another table?
Account number? If so, is the account number the primary key?
You may have heard that you can't create a relationship between two
autonumber fields, which is true for all practical purposes. I believe that
you can create a one-to-one relationship between two non-autonumber PK
fields, but your questions lead me to wonder how you go about entering data
into the two tables. Perhaps Bank and Ledger are standard terms with
specific meanings in accounting or something, but if so I am unfamiliar with
their usage. Are they intended to be identical tables that are being
reconciled by mean of double data entry, or something like that?

"accessuser via AccessMonster.com" u28669@uwe wrote in message
news:6eba07c59a848@uwe...
you can't link two primary keys, right? only primary key to foreign key,
isn't it right??


thanks

Jason Lepack wrote:
A primary key is something that uniquely identifies a record. You
will have two, one in each table, but they will be the same.

Cheers,
Jason Lepack

On Mar 4, 10:29 am, "accessuser via AccessMonster.com" u28669@uwe
wrote:
I am trying to create an Account Reconciliation Databse which will
compare
two tables. Two tables are Bank and Ledger. Both tables have same
fields.
[quoted text clipped - 7 lines]
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

User will import their Bank files (cleared items) to Bank table. and input
their own record (general ledger) items to Ledger table. Both tables have
same fields.

"Account #"
"Date"
"Check #"
"Amount"
"Description"
"Status"

User will run a command called "Match" and two files will run against each
other and list the ones that matches from both table, criteria based on check
# and amount.

The ones that do not match will be listed under "UnMatched"

I did some testing, and then i set the Bank table "Check #" as the primary
key,, and just let the Ledger Table have an ID primary key which doesn't do
anything for me. I dont know if i should delete it or just have a pk
assigned.

How do you set PKs for two tables with same field, and contain mostly the
same information?

Thanks.

BruceM wrote:
How is a record in one table associated with a record in another table?
Account number? If so, is the account number the primary key?
You may have heard that you can't create a relationship between two
autonumber fields, which is true for all practical purposes. I believe that
you can create a one-to-one relationship between two non-autonumber PK
fields, but your questions lead me to wonder how you go about entering data
into the two tables. Perhaps Bank and Ledger are standard terms with
specific meanings in accounting or something, but if so I am unfamiliar with
their usage. Are they intended to be identical tables that are being
reconciled by mean of double data entry, or something like that?

you can't link two primary keys, right? only primary key to foreign key,
isn't it right??
[quoted text clipped - 17 lines]
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

I doubt that Check# will make a good PK, at least not if you mean what is
usually meant by Check#. Two customers could have the same check number.
It looks to me as if you have two separate entities in this table: Account
and Check. Each Account will have many checks. The usual setup would be to
have an Account table containing account information such as Number, Name,
Address, and so forth. A related table would contain information about
individual checks.

tblAccount
AccountID (PK) This could be the account number
Name
etc.

tblLedger
LedgerID (PK)
AccountID (foreign key)
CheckNumber
CheckDate
CheckAmount
etc.

There is a one-to-many relationship between Accounts and Checks.

tblLedger is much as you have described, but I don't see how you can create
a PK from the existing fields except by something like combining CheckNumber
and AccountID into a compound key, unless this is a database to be used by a
single user to reconcile a single account, in which case CheckNumber could
serve as the PK.

If this is a database for reconciling a statement with a customer's own
records, you could have two subforms on the account form: one for the
imported information and one for the input information, which could be
compared to a record on the imported data subform.

"accessuser via AccessMonster.com" u28669@uwe wrote in message
news:6ebc6960d4fd9@uwe...
User will import their Bank files (cleared items) to Bank table. and
input
their own record (general ledger) items to Ledger table. Both tables have
same fields.

"Account #"
"Date"
"Check #"
"Amount"
"Description"
"Status"

User will run a command called "Match" and two files will run against
each
other and list the ones that matches from both table, criteria based on
check
# and amount.

The ones that do not match will be listed under "UnMatched"

I did some testing, and then i set the Bank table "Check #" as the primary
key,, and just let the Ledger Table have an ID primary key which doesn't
do
anything for me. I dont know if i should delete it or just have a pk
assigned.

How do you set PKs for two tables with same field, and contain mostly the
same information?

Thanks.

BruceM wrote:
How is a record in one table associated with a record in another table?
Account number? If so, is the account number the primary key?
You may have heard that you can't create a relationship between two
autonumber fields, which is true for all practical purposes. I believe
that
you can create a one-to-one relationship between two non-autonumber PK
fields, but your questions lead me to wonder how you go about entering
data
into the two tables. Perhaps Bank and Ledger are standard terms with
specific meanings in accounting or something, but if so I am unfamiliar
with
their usage. Are they intended to be identical tables that are being
reconciled by mean of double data entry, or something like that?

you can't link two primary keys, right? only primary key to foreign
key,
isn't it right??
[quoted text clipped - 17 lines]
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

Sorry, I should have explained myself more clear. This database will be used
by single customer, one account only. Both ledger and bank tables might have
same repeatitive account numbers. That's why i can't set it as PK. I hope i
am on the right track by setting it to check_num before i go deeper with this
database. i dont want to do everything over again.

Is it ok to use underscore for field name, such as check_num? I used the
check # before and seems to mess up my VBA codes.

Thansk.


BruceM wrote:
I doubt that Check# will make a good PK, at least not if you mean what is
usually meant by Check#. Two customers could have the same check number.
It looks to me as if you have two separate entities in this table: Account
and Check. Each Account will have many checks. The usual setup would be to
have an Account table containing account information such as Number, Name,
Address, and so forth. A related table would contain information about
individual checks.

tblAccount
AccountID (PK) This could be the account number
Name
etc.

tblLedger
LedgerID (PK)
AccountID (foreign key)
CheckNumber
CheckDate
CheckAmount
etc.

There is a one-to-many relationship between Accounts and Checks.

tblLedger is much as you have described, but I don't see how you can create
a PK from the existing fields except by something like combining CheckNumber
and AccountID into a compound key, unless this is a database to be used by a
single user to reconcile a single account, in which case CheckNumber could
serve as the PK.

If this is a database for reconciling a statement with a customer's own
records, you could have two subforms on the account form: one for the
imported information and one for the input information, which could be
compared to a record on the imported data subform.

User will import their Bank files (cleared items) to Bank table. and
input
[quoted text clipped - 47 lines]
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1

It is best to use only alphanumeric characters (no spaces) and underscores
for names. Symbols such as number signs often have other meanings in
Access, and can send the code in unexpected directions. Underscores are
fine.

If both the ledger and bank tables have repetitive account numbers then I
expect they almost surely have or will have repetitive check numbers. If
the ledger and bank tables contain the account number over and over it may
be that the structure is not normalized properly (and data are being stored
redundantly). If a single customer is importing just his or her information
into the table, how is other account information excluded? Or do you mean
that a customer may have several accounts, all of which are to be included
in the imported information? If so, isn't there a chance that two checking
accounts could have the same check number? Any value that is guaranteed to
be unique may be used as the PK; I'm just asking if CheckNumber is that
field.

If the top level of the database is the customer then it may not be
redundant to store the AccountNumber, since each customer may have several
accounts, each of which has its own list of transactions.

Without knowing
"accessuser via AccessMonster.com" u28669@uwe wrote in message
news:6ecadc795e035@uwe...
Sorry, I should have explained myself more clear. This database will be
used
by single customer, one account only. Both ledger and bank tables might
have
same repeatitive account numbers. That's why i can't set it as PK. I
hope i
am on the right track by setting it to check_num before i go deeper with
this
database. i dont want to do everything over again.

Is it ok to use underscore for field name, such as check_num? I used the
check # before and seems to mess up my VBA codes.

Thansk.


BruceM wrote:
I doubt that Check# will make a good PK, at least not if you mean what is
usually meant by Check#. Two customers could have the same check number.
It looks to me as if you have two separate entities in this table: Account
and Check. Each Account will have many checks. The usual setup would be
to
have an Account table containing account information such as Number, Name,
Address, and so forth. A related table would contain information about
individual checks.

tblAccount
AccountID (PK) This could be the account number
Name
etc.

tblLedger
LedgerID (PK)
AccountID (foreign key)
CheckNumber
CheckDate
CheckAmount
etc.

There is a one-to-many relationship between Accounts and Checks.

tblLedger is much as you have described, but I don't see how you can
create
a PK from the existing fields except by something like combining
CheckNumber
and AccountID into a compound key, unless this is a database to be used by
a
single user to reconcile a single account, in which case CheckNumber could
serve as the PK.

If this is a database for reconciling a statement with a customer's own
records, you could have two subforms on the account form: one for the
imported information and one for the input information, which could be
compared to a record on the imported data subform.

User will import their Bank files (cleared items) to Bank table. and
input
[quoted text clipped - 47 lines]
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200703/1