?
Solved

Key fields question

Posted on 2012-03-23
8
Medium Priority
?
284 Views
Last Modified: 2012-03-31
Beginner here.

I have two tables with a many to many relationship (invoices and their respective bank payments)

So I use a join table with the two foreign index fields.

Now then, I want to create many copies of these respective tables, such as Ledger01 Ledger02 and so on.

In my original spreadsheet layout the index field was/is called OurRef and so is the index field in the Bank01 and Bank02 etc also called OurRef. This approach facilitated pivot table reports with a pivot table helper column specifying the Source e.g Ledger01 or Bank01 etc.

I find that I am prevented from using the same name of (foreign) index field twice in the Joining Table (not a surprise, but a path to travel).

So even if my OurRef fields are "surrogate" keys, I can't use the spreadsheet approach, because there can be only one (Highlander?).

I am thinking this means I create a special surrogate key in each table and use that as the foreign key in the Joining table, but present the "old" key to the user if any such presentation is required?

Anthony
0
Comment
Question by:Anthony Mellor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Accepted Solution

by:
LCSandman8301 earned 1500 total points
ID: 37758470
the name is the only thing that is required to be unique so with that said you can do the following

create table Bank: BankId (AN), BankName

Create Table Invoice: invoiceid (AN), invoicename

Create Table Bank_Invoice : Bank_InvoiceId (AN), BankId (FK to Bank), InvoiceId (FK to Invoice)

also if for example you wanted parent child relationships on the Bank table you could do the following:
Create table Bank: bankid(AN), bankname

Create Table ParentChild_bank: parentbankid(FK to bank), childbankid (FK to bank)

all you need to do is make sure you name the two FK relationships differently

I hope this helps
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 37760516
You may also want to think about why you are creating multiple copies of the tables in Access...  Trying to mirror exactly what you do in Excel when you are using Access is sometimes  a mistake.  For example if you were using sheets Bank01 Bank02 etc for different banks it's a lot better to just put a Bank ID in your single bank table and then create a pivot "WHERE BANKID=Whatever"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37760975
I am also curious about the need to "Copy" the tables at all...
Sounds like creating redundant data to me...

Why not create a query?

SELECT *
FROM YourTable
WHERE Ledger='Ledger01'

...or something similar...?

JeffCoachman
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37761060
yay, I didn't think of that until after posting my question - the "why multiple copies thing".

edit: actually, even having the two basic tables is questionable. "Sun Account" is famous for having everything in one Nominal (General) Ledger.

Don't know the arguments for this - in fact I have always liked that idea, it being purist debits and credits - while appreciating the tables involved are unlikely to be one flat file.

edit: not finished reading responses.

Anthony
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761275
Then this also points to kmslogic's post about trying to simulate Excel sheets with Access table data.

You have to have a fairly solid foundation in Normalization and table relationships first.
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37761539
the bit I am studying is how join tables (to solve many-to-many relations) and key fields work, for which of course I need at least two tables to join. In this case whether I should or should not have x other tables is not important to me, if clearly capable of distracting me as it's all very interesting.

I now see  LCSandman8301's answer is the sort of understanding I am after, had to be the last response I read.

What does AN denote? I presume FK is Foreign Key.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761607
AN=(A)uto(N)umber
0
 
LVL 9

Author Comment

by:Anthony Mellor
ID: 37763290
I can see that ID: 37758470 is probably the answer to my question; but as it is not written in the same language as my question, struggle as I am (I have read it many times), I am having difficulty relating the answer(s) to my question - you see I understand my question, but not entirely the answer. A beginners' problem.



Anthony
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question