Could someone look at my Access relationship model 1

[garrowsmith]

Hi Folks,

I've been racking my brains to remember all the database rules, and think I've stuck to them.

For some reason when I try and match a username to a particular project with the query wizard I get a cartesian product of all records.... eg

username | project title
-------------------
username project 1
username2 project 2
username3 project 3
username project 1
username2 project 2
username3 project 3
username project 1
username2 project 2
username3 project 3

Does it look like I've got my links wrong between tables/keys? I'm trying to get the relationships where a username is associated with an individual within a company. A company has multiple projects, and a user account has access to multiple projects.

I've got an diagram for this here:

http://dev.prm.co.uk/accessDB/erd.gif

Any pointers would help!!!

Cheers,

Graham.

p.s This isn't commercial or anything like that - I'm just playing around with this to try and recall some of the old stuff I learned at Uni.

 

[Quehay]

Someone else had almost the same issue. See the response
in the Tables/Relationships forum: thread700-306352

 

[Quehay]

Just get your User, UserAccess, and Project tables into the same query design window--with the referential integrity in place (as you've done) the joins will take care of themselves.

 

[garrowsmith]

Thanks Quehay, that was a big help - I didn't realise that you needed to put tables within the query window, and because I didn't have the intermediate table I was getting the cartesian product of all recs. Problem solved!

I haven another issue tho - and again I'm all ears if you can think of a solution - I want to make an 'admin' account that can view all projects and make edits. Would I just perform a SELECT * FROM projects to give me the master project list?

Will I have to hard code this account in the ASP pages or am I better creating an 'Admin' user account that has an entry for every project within the user_projects table.

Eg: TABLE: user_project
UserID | ProjectID
------------------
1 | 1
1 | 2
1 | 3
2 | 1
3 | 2
4 | 3

I haven't started coding for the updating side of things, but it seems a headache to maintain and username table, every reference within the 'user_project' table etc. Is it easier than I think, or will the referential update/delete checkboxes help take care of this in the relationship manager?

 

[Quehay]

Graham,

Are you working in an environment with NT security? If so, you could grab the username and use that for security (if you don't expose db window and just allow forms access to data you can have a CheckUser function in the form Open event and create the record set for the form based on the user).

If you're using ASP I'd guess this isn't so (unless an intranet).

The access security model will give fine granularity for table additions/edits, etc. but it's not something to enter into without a lot of thought, and because the ASP pages are stateless it probably won't work here. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com