Problems with

[berntis]

Hi all,

we are trying to make a project database for our office to plan man hours a few weeks ahead. We found a nice template from the microsoft web site and addded a few things to it.

To show the planned work we used a pivot table, and this works fine. The problem, however, is that we can't get some descriptions to work in a query because of some "inner joins" error message. I think the problem lies with some of the relationships, but we can't seem to figure it out..

I am posting some screenshots, hoping that some of you would have an idea about this!

If any of you could help with this it would be highly appreciated! Please let me know if anything is unclear and i'll try to explain better...


Regards,

Berntis

 

[tehNellie]

You have special Characters in your column names AND spaces (in combination with underscores and CamelCase!), You also seem to have attachments duplicated in 3 different tables. I'm guessing as a lookup column (never used them myself, they're an even bigger work of Satan than the other transgressions noted) rather than as a related column to the AttachmentTable.

ID is an ambiguous column name, when you see it in a query, which ID are you referring to. EmployeeID, ProjectID, DeliverableID are better alternatives. Your foreign Keys also don't reflect what they relate to either, even if you insist on calling all your primary keys "ID". Owner in the projects table for example actually refers to the Employee.ID column, if you call it "EmployeeID" it's immediately apparent just by looking at the column list that it's probably a foreign key to the employee table.

in terms of your query:

You can't use JOINs in that manner in Access hence you're getting an Error that you have ambiguous JOINS going on.

you can't do:

SELECT columns
FROM t1
[b]LEFT JOIN[/b] t2 ON
  t2.ID = T1.id
[b]INNER JOIN[/b] t3 ON
  t3.id = t2.id

Once you've started using a LEFT JOIN you basically have to continue using LEFT JOINs or break out into a separate query. I don't believe you can LEFT JOIN from one table and INNER Join from a second onto another either.

i.e.

qryINNERJoin
SELECT columns
FROM t2
INNER JOIN t3 ON
 t3.ID = t2.ID

can now be called from the original query

SELECT columns
FROM t1
LEFT JOIN qryINNERJoin ON
 qryINNERJoin.ID = t1.ID

 

[berntis]

Hi again!
Things have come up and i won't be able to look at this right now, but perhaps over the weekend..

Thanks for your input tehNellie, it's much appriciated!

Berntis

 

[jdraw]

In addition to the comments from tehnellie, I think you should go back to the template and show us the relationships. Then make a list of the things you want to add. This list could be in the terms of "business rules", or "entities" with some description of what each is and how it relates to the rest of the proposed structure.

 

[berntis]

This was a downloaded template that we started to modify. That's why the names are messed up..
But that's sorted now, including the "ID"-fields, where I've added "EmployeeID, ProjectID" etc..(Thank you for that tehNellie!)

But the problem where it won't show the ProjectName is still there, and I haven't figured out how to fix it, wich is making me loose my mind slightly...
I tried to get the ProjectName from a different query, but then i suddenly got multiple values for the same field, and i have no idea why..

Instead of taking alot of screenshots I'm gonna upload the entire database here, so one of you geniuses can go in and see what it is that I do wrong.
The problem is in frmPivotRessursOversikt, and then in the "Project-column". This only shows the Project-ID, and i want it to show the entire Project-name.

Any help on this would be much appreciated, hope one of you can take the time...

Berntis

 

[berntis]

Hi!

anybody have any clue on this? I'm still stuck..