July 9, 2008 06:57am pdt

1. boag2000 95,512
2. LSMConsu... 82,054
3. capricorn1 70,100
4. DatabaseMX 57,739
5. peter57r 44,941
6. harfang 29,826
7. matthews... 28,872
8. jimhorn 18,990
9. hnasr 15,150
10. jefftwilley 14,700
11. frankytee 13,650
12. nexusnation 10,400
13. GRayL 10,347
14. eghtebas 10,000
15. RgGray3 9,596

1. LSMConsu... 147,169
2. DatabaseMX 133,097
3. capricorn1 113,108
4. boag2000 103,487
5. peter57r 87,395
6. jefftwilley 60,468
7. matthews... 57,206
8. harfang 48,806
9. jimhorn 43,008
10. GRayL 30,779
11. frankytee 30,542
12. JDettman 28,775
13. stevbe 21,852
14. dqmq 21,760
15. MikeToole 21,750

02.19.2008 at 03:44PM PST, ID: 23176099

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.6

Many to Many Relationship

Zones: Access Architecture/Design, SQL Server 2005

Tags: VBA, Microsoft Access

I thought I had this many to many thing figured out and now Im very unsure.
I have these tables
tblClients
ClientFileNo PK(auto number)
SSN
FirstName
Etc

tblFunds
FundId PK(auto number)
FundSourceLkUpId (Example Data: ES or VS&.see below)
FundFromDate
FundToDate

tblLkUpFunds (this is just a lookup table forFundSourceLkUpId above)
FundSourceLkUpID PK(text)
FundingSourceDesc
      Example data would be
Fund Description
ES        Educational Services
VS        Veterans Services

Now a Client can have many different Funds and a Fund can be for many Clients.
So I created a linking table for my many to many relationship
tblClientFund
ClientFileNo PK
FundID PK

When I look at the tblClientFund data I can see that the ClientFileNo repeats. But never does the FundId repeat. With the structure I have now I dont think it ever would. The dates that are part of the record in tblFunds make it highly unlikely. Even if 2 Funds had the same dates the dates are allowed to change.
Im using FundID as the PK because the dates are different but its actually the field FundSourceLkUpID that is the Fund that many Clients can have. But I cant make FundSourceLkUpID the key because it would repeat in the tblFunds. Its the dates that make it unique.

Is this not a true many to many? Or how do I need to change my structure to make it work.
Please help!

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: BobRosas

Solution Provided By: LSMConsulting

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

02.19.2008 at 03:50PM PST, ID: 20933796

Rank: Master

cmangus:

You have described what I know to be a true many-to-many table. The keys from each outer table would appear in the table in the middle. No other fields are needed.

02.19.2008 at 03:58PM PST, ID: 20933850

BobRosas:

But it doesn't appear to be working. The way it's set up now I can't think of any situation where the FundID would ever repeat in tblClientFund which is the many to many table. I think some how FundSourceLkUpId needs to be the key of tblFunds but that doesn't work because of the dates. I can't help but think I'm missing something.
Any thoughts?

02.19.2008 at 05:29PM PST, ID: 20934284

Rank: Genius

chapmandew:

What do you mean it doesn't work because of the dates? Typically when you have a many-to-many relationship, you have a table in the middle. The middle table is comprised of the primary keys of the two tables that it connects. The primary key of the middle table is usually the combination of the primary keys of the two tables it connects.

02.19.2008 at 06:02PM PST, ID: 20934403

Rank: Master

LSMConsulting:

In this table:

tblClientFund
ClientFileNo PK
FundID PK

What is FundID storing? Is it storing tblFund.FundID?

02.19.2008 at 06:20PM PST, ID: 20934480

BobRosas:

chapmandew...Thanks for your response.
Maybe I'm just over thinking this. But AcctNo can have multiple Funds. And Funds can have multiple AcctNos. So because of that statement, I thought that AcctNo should be one field in the linking table...and it is. But now I'm thinking FundSourceLkUpID should have been the other linking field. That is the actual field that can relate to more than one AcctNo. FundId will never repeat in tblClientFund because it's just a means to allow FundSourceLkUpID to repeat in tblFund so that each fund can have multiple dates. But since FundSourceLkUpID can't repeat of course just that change wouldn't work. I thought I'd have to split things up in more tables maybe. This is very confusing for me. I really appreciate the help of all of you!

LSM Consulting, also thanks for responding
Yes FundID from tblClientFund is storing the same data as tblFund.FundID

02.20.2008 at 04:21AM PST, ID: 20937056

Rank: Master

LSMConsulting:

Please explain how FundID and FundSourceLkUpId are related - what does each Table store? Why does tblFund need FundSourceLkUPID? That column should somehow "describe" the Fund, or be a "property" of the Fund. A few sentences or a short paragraph should do it.

Once you've got that down, then explain how the AcctNo is related to these data as well. Is this part of tblClientFund? Is it the PK of that table? Same as above - few sentences, etc.

For the Record, you should store PrimaryKeys in your MTM Join table ... so storing FundSourceLkUpID would NOT be appropriate unless that is also the PK of your Fund table.

Normalizing your data can be very confusing, and it's compounded by the fact that there is no one right way - every dataset is unique. Hang in there, though. You're on the right track and, unlike many we see here at EE, you seem to actually want to get this right, and not just "make it work".

02.20.2008 at 08:57AM PST, ID: 20939659

BobRosas:

LSMConsulting,
FundId is an auto number/PK assigned to each record in tblFund. FundSourceLkUpId is the field that contains the name (or an abbr version) of the Fund. In my table structure FundSourceLkUpId repeats in tblFund so it cannot be the PK but maybe that's what I need to change. FundSourceLkUpId however is the PK of tblLkUpFunds which is just a lookup table. It only contains the Fund and it's description. The other data stored in tblFund is what date the fund started and ended. That is the reason for my confusion. The way I see it the FundId would never repeat in the linking table because I'm not just linking via Fund but also dates associated with each Fund.
AcctNo is also an auto number/PK that's assigned to each Client. And it's part of the key to the linking table. It does repeat (in the linking table) each time the Client has another Fund assigned. Which is what I expect.
Right now only AcctNo and FundID (both PK's) are stored in my MTM table. The reason I thought this might be wrong is because the FundId is never going to repeat in the MTM table. Because of that maybe a MTM table isn't even needed. Maybe it's just a one to many from tblClients to tblFuinds. Even tho a Client can have more than one Fund it doesn't appear I'm using the MTM table to reflect that.

Thanks for your words of encouragement. I'm trying to look up examples of MTM data sets to maybe understand it better. For me it all boils down to the FundId not repeating in the MTM table. If that's true then how could I not have somthing wrong.
Thanks again!

02.20.2008 at 11:51AM PST, ID: 20941175

Rank: Master

LSMConsulting:

A "Fund" is a unique record in tblFunds that is described by (a) the FundSource and (b) a Date Range. Is that Correct?

A Fund can never be related to more than one Client. Is that correct?

If the above two are True then just store the PK of the Client in the Fund table. this would relate your Fund to the Client, and would allow a single client to be related to Many funds. This would mean that a Fund could not be related to multiple Clients, but a FundSource could (since you're defining a Fund as the FundSource and a DateRange).

02.20.2008 at 12:20PM PST, ID: 20941458

BobRosas:

If you change "Fund" to "FundId" in your first 2 statements above then the above is true and I totally agree...that makes sense!
But if a Client can have more than one FundSource (which they can) then should I be rewriting my tables? Because I know that an AcctNo can have multiple FundSources, and FundSources can have multiple AcctNos. So I keep trying to force ClientNo and FundSource to be the PK's in the M2M table but then the functionality doesn't work at all. So maybe it's not a true M2M because of...something? But that's the part that makes it hard to move on.
You really seem to have a grasp on this! Thanks so much for helping out!

02.20.2008 at 02:34PM PST, ID: 20942784

Rank: Master

LSMConsulting:

How are ClientNo and AcctNo related?

I'm more concerned with what exactly constitutes a "unique" record in your Funds table. In other words, what Column or group of Column in that table would make up a unique record?

I'd think so, but we'd need to see more before we could determine this. Can you take a screenshot of your relationship diagram and post it here (please post it as an Image file and not a .doc file).

02.20.2008 at 02:37PM PST, ID: 20942799

Rank: Master

LSMConsulting:

And I'd again urge you to write this out, but WITHOUT using Table or Column names ... in other words, don't write "In tblFunds I'll store blah blah ", but instead write "This database needs to track Clients and their related Funding. Funding can come from multiple sources blah blah blah" - no techno-speak at all, just write out what you would present to your boss as a high level overview of the database and why you need it. Once you get that, post it back here and we'll flesh it out.

02.20.2008 at 03:38PM PST, ID: 20943307

BobRosas:

In my example ClientNo and AcctNo are the same field. I apologize for switching names in mid stream. Unfortunately when I post a screen shot it will also cause confusion because of different names that I shortened in hopes of making this easier. I'll just post the relationship between these 4 tables. I have over 75 tables, I don't think you want the whole thing.
What makes a unique record in tblFunds (right now) is FundId (it's an AutoNo that does not repeat)
So to try and explain without techno talk...
Our company has Clients that come to us and request a service. They pay for the service with Funding that is available to them. Each Client can have more than one Funding. Also any one particular Fund, for example the Veterans Services Fund, can Fund more than 1 client. When the Client comes to us we want to record what Fund (from a dropdown selection) they will be using. We also need to know the start date of that fund and the end date of the fund.
Reports will need to show what Clients are being Funded by any given Fund. For example...
The Veterans Services Fund(VS) is funding 4 different Clients. Clients File Number 2451, 8596, 3279 and 6543.
We also need a report showing what Funds a Client is using. For example...
Client File Number 2451 is being funded by The Veterans Services Fund(VS) as well as the Educational Services Fund(ES) and the AB Fund (AB).

"VS", "ES" and "AB" would all be in the FundServiceLkUpID field of both tblFunds and tblLkUpFundSource
"Veterans Services Fund" and "Educational Services" would all be in the FundDesc field of tblLkUpFundSource
Atleast this is how it is now. Maybe that is what needs to change.

I hope all of this helps. I was hoping that by explaining this to someone else I would figure it out on my own...not happening yet anyway!
Thanks again!

M2M.bmp (715 KB) (File Type Details)

4 tables used in my M2M relationship

02.20.2008 at 06:13PM PST, ID: 20944363

Rank: Master

LSMConsulting:

Okay ... from your writeup, then, a Client can be Funded by more than one source, and a Source can be used for more than one Client ... so we're back to a Many-To-Many join as you have illustrated.

Are the StartDate and EndDate for a FUND always the same, or do they change from Client to client ... so if I came and requested a service which was to be funded via the ABC fund (which had a StartDate of 02/01/2008 and an EndDate of 3/01/2008) and then YOU came in and requested a service which was to be funded with the ABC fund, would those Start and End dates still apply?

From what you've posted, you would have to build a Fund record before you can add a record to the Join table. This would typically be done BEFORE you even attempted to add a Fund to a client (perhaps on a popup form). Once you've done that, you'd then store the data as you have it mapped. And, unless the Start and End dates change for each Client, then this would be fairly standard in a relational db (i.e. lookup records must exist before you build the Child records).

So your subform would have nothing to do with tblLkUpFundSource ... this would already be associated with the Fund, and your enduser would simply choose the Fund.

That said: You could build a subform with this functionality, but it would require quite advanced techniques to get right and to make sure that the data was handled correctly ...

<What makes a unique record in tblFunds (right now) is FundId (it's an AutoNo that does not repeat)>

An Autonumber field should never be what makes a record unique ... an AN field should have absolutely no meaning to the DATA ... it may indeed be used as a Primary Key, but you must have another column, or combination of columns, that would make a Unique Record. An AN field is just an arbitrary number assigned to the table row ... nothing more.

02.21.2008 at 08:18AM PST, ID: 20949155

BobRosas:

The StartDate and EndDate for any one Fund would usually be different for each Client.
So Client 2451 may be funded with ABC from 1/1/08 to 1/3/08 and Client 8596 would be funded with ABC from 2/5/08 to 2/9/08

With this being true are we back to the drawing board with this? I do aplogize. I didn't think it would be this hard.

Currently, I have all the funding sources listed in a look up table (tblLkUpFundSource) that I use as a dropdown field for the user to select from. Then the from and to dates for that fund are added to the record and linked (via the AN) back to a specific Client. I believe at this point the record then becomes unique because of the dates. But the relationship with the Fund (w/o the dates) should still be M2M...right?

Based on your explanation of tblFunds, my table structure is obviously wrong. The AN is what makes the record unique. The FundSource and the Dates could repeat (and often do) so I assigened an AN so that a specific record(s) could be linked back to the Client. To add to the confusion I don't know how to fix this table either but I'd be glad to post another question for that.

Thanks for your persistance!

02.21.2008 at 09:47AM PST, ID: 20950018

Rank: Master

LSMConsulting:

Let me mull this over for a bit ...

02.21.2008 at 05:19PM PST, ID: 20953795

Rank: Master

LSMConsulting:

Still mulling ...

02.21.2008 at 05:36PM PST, ID: 20953879

Rank: Master

LSMConsulting:

Download this and try it:

http://www.infotrakker.com/downloads/bobrosas.zip

I've been trying to punch holes in the current setup, and I really can't. From what you're describing, the tables are currently setup correctly. The ONLY reason I'd think they're not would be if you want to re-use a "Fund" record (i.e. a FundSource + StartDAte + EndDAte), in which case you'd HAVE to pre-define those Funds ... you could do it on the fly, but it'd be a fancy bit of coding.

The download allows you to add xx number of Funds to a Client, and select the Source and Start and End dates ...

Accepted Solution

02.22.2008 at 09:48AM PST, ID: 20959642

BobRosas:

Thank you so much for your help! What you sent is basically the same thing I already have. One thing I found that was different is that the record source of your subform did not include FundId from tblFunds as one of the fields. When I tried to do the same I got the message...
"Cannot add record(s); Primary key for table tblFunds not in recordset.
That could be because I'm attached to a SQL database instead of access. When I include the field I don't get the error.

Another thing is that I added the key fields to my form and set them to not visible for the user and visible for me when I tested. I don't see why that would be a problem but I went ahead and deleted them from my form. So far all appears to work but remember the original problem was sporatic and not easily duplicated.

Because FundID in the M2M linking table will never repeat it doesn't seem like a true M2M relationship but I feel better knowing an expert agrees with my setup.

I'll close this question and post more as needed. Thanks again for all your work. You went above and beyond!

LSMConsulting

02.22.2008 at 09:56AM PST, ID: 20959698

< One thing I found that was different is that the record source of your subform did not include FundId from tblFunds as one of the fields.>

The form's Recordsource does include it, and it's updated when the new record is created. That's the tricky part with these ... IIRC, your relationship was based on the lookup field, this one is not. That's the real difference.

BobRosas

02.22.2008 at 10:06AM PST, ID: 20959790

Sorry for not being clear. The record source does include FundId from tblClientFundSource but not FundId from tblFunds. I mean it links the 2 so in Access I guess you don't have to display both of them down below in the field section. Your query displays 5 fields (and it works!) but I need all 6 with FundId repeating for both tables. I'm just hoping that sql was somehow confused with me having the extra (but not visible) fields on the form. It seems to be my only chance at solving this thing.
Thanks again! You've been great!

20080236-EE-VQP-29 / EE_QW_2_20070628