Key fields question

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 MellorChartered AccountantAsked:
Who is Participating?
LCSandman8301Connect With a Mentor Commented:
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
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"
Jeffrey CoachmanMIS LiasonCommented:
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?

FROM YourTable
WHERE Ledger='Ledger01'

...or something similar...?

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Anthony MellorChartered AccountantAuthor Commented:
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.

Jeffrey CoachmanMIS LiasonCommented:
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.
Anthony MellorChartered AccountantAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Anthony MellorChartered AccountantAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.