[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Key fields question

Posted on 2012-03-23
8
Medium Priority
?
285 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What we learned in Webroot's webinar on multi-vector protection.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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