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
LVL 9
Anthony MellorChartered AccountantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LCSandman8301Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kmslogicCommented:
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
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?

SELECT *
FROM YourTable
WHERE Ledger='Ledger01'

...or something similar...?

JeffCoachman
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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.

Anthony
0
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.
0
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.
0
Jeffrey CoachmanMIS LiasonCommented:
AN=(A)uto(N)umber
0
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.



Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.