Solved

Key fields question

Posted on 2012-03-23
8
276 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:anthonymellorfca
8 Comments
 
LVL 6

Accepted Solution

by:
LCSandman8301 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 9

Author Comment

by:anthonymellorfca
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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:anthonymellorfca
Comment Utility
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
Comment Utility
AN=(A)uto(N)umber
0
 
LVL 9

Author Comment

by:anthonymellorfca
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now