addition to table messes up query (1 Viewer)

[rainman89]

Hi all,
i have a table called expense that i have tied to a case table... i also want this tied to the employee table, but when i add the employeeID field to the expense table it messes up all my queries that i had previously created... the queries merely reference the expense amount that is located within the expense table... is there something that i am doing wrong? (obviously there is) but i would like to know why this is happening

 

[llkhoutx]

I don't see well in the dark.

How is the employee table linked in the query?

Merely adding a field is of no consequence unless its a linked field in the query. EmployeeID sounds like the primary key for the empoyee table. I wuld think that that field is being linked.

sounds like you are not linking your tables/queries correctly in the query.

Look at the Relationships of your database. It might give you a clue.

A post of a snapshot of your query would have helped immensely.

 

[rainman89]

Thank you for your reply. sorry i have not gotten back to u sooner. i was out of the office all weekend.

As per your request i have created snapshots of my relationships. the first one is when it works (w/o the employeeID relationship to texpense)
2nd is with.

Also i have attached my query snapshot..

 

[Mike Smart]

Thank you for your reply. sorry i have not gotten back to u sooner. i was out of the office all weekend.

As per your request i have created snapshots of my relationships. the first one is when it works (w/o the employeeID relationship to texpense)
2nd is with.

Also i have attached my query snapshot..

Hi Ray

When I see a relationship going around in a cirle in a schema (relatepic2) it always rings alarm bells. Sometimes it is OK but it is more often a sign that there is a modelling error.

I couldn't see anything wrong with your schema until I began to think that Time is an expense as well as any other type of expense (such as disbursements). To this end may it be that you don't need to link the time table into the case table at all.

Something like a CASE has EXPENSES and an EMPLOYEE can be the cause of an EXPENSE (or perhaps there are expenses that do not involve an Employee like registry fees). Sometimes an Employee works TIME that creates an EXPENSE that is attached to a CASE. Can you see where I'm coming from? Engineered in this way your queries might be easier to create as you have a true hierarchy.

 

[rainman89]

Yes i can see where u are coming from.... and i can see the circles u are referring too.
perhaps my overall modeling is wrong.
i want the link between expenses, time and cases to be the most important part. i do need to know the employee and how they are linked to the expense, but not as much as how the expense is related to the case. if i remove the link between the time and the case how would i know that they are related?

 

[Mike Smart]

Yes i can see where u are coming from.... and i can see the circles u are referring too.
perhaps my overall modeling is wrong.
i want the link between expenses, time and cases to be the most important part. i do need to know the employee and how they are linked to the expense, but not as much as how the expense is related to the case. if i remove the link between the time and the case how would i know that they are related?

Because, in this scenario, A case would be related to an Expense which, in turn, (if it is a time-related expense) would be related to Time which would be related to the Employee booking the time.

I'd go back to first principles and confirm the relationships by writing them down as in:

One Employee may book one Time entry but a Time entry is only ever related to one Employee.

... and so on.

Then relate it back to your schema and make sure that the entities (tables) are all connected with the correct relationships.

 

[rainman89]

"One case may have many time entries but a time entry will only have one case
One case may have many expense entries but an expense will only be applied to one case

One employee may have many time entries but a time entry will only have one employee
One employee may have many expenses but an expense will only have one employee

One time entry will only have one employee
One time entry will be applied to only one case"

does this make sense?" it seems to to me.. but how do i change relationships from one to many to one to one?

 

[Mike Smart]

"One case may have many time entries but a time entry will only have one case
One case may have many expense entries but an expense will only be applied to one case

One employee may have many time entries but a time entry will only have one employee
One employee may have many expenses but an expense will only have one employee

One time entry will only have one employee
One time entry will be applied to only one case"

does this make sense?" it seems to to me.. but how do i change relationships from one to many to one to one?

The one that is missing is:

One expense is associated with zero one or more time entries (one to many)

OR is it:

One expense is associated with one and only one time entry.

That's the key to your problem I think.

Use this real world example to illustrate your answer if there is any confusion:-

Employee Bill works for three hours on Thursday on the Murdoch case. His hours are billed at £30/hour resulting in a charge of £90 to Murdoch. There's also a stamp duty charge in today for £100 that has also been automatically re-charged to Murdoch at cost.

 

[rainman89]

i was trying to get around the fact that an expense needed to be related to a time entry... wanted them to be seperate. When creating the tables, that is why i created 2 seperate tables. i originally had them as one...
you are saying however that i need to have the link between the 2...... in that case it would be "one time entry will have one and only one expense entry" which could also be no entry correct?

 

[Mike Smart]

i was trying to get around the fact that an expense needed to be related to a time entry... wanted them to be seperate. When creating the tables, that is why i created 2 seperate tables. i originally had them as one...
you are saying however that i need to have the link between the 2...... in that case it would be "one time entry will have one and only one expense entry" which could also be no entry correct?

I sort of thought that might be the case. In other words it is a "one-to-one" relationship. Your first instincts were right, the time information should simply be expressed as fields in the expense table and you'll find that you can then simply create any query you can describe in words. One-to-one's aren't generally used in the real world, when they are it is usually a security work-around so that confidential fields can be protected by splitting the table.

Look forward to seeing your next draft... and I think you'll find that your earlier problem vanishes along the way.

 

[rainman89]

is there a simplier solution then redesigning the whole DB around that one change? everything else that i have works fine... just that one query doesnt give me the results im looking for

perhaps creating a query that is based on the case and expense tables only? sinc ethey are still linked together?

 

[Mike Smart]

is there a simplier solution then redesigning the whole DB around that one change? everything else that i have works fine... just that one query doesnt give me the results im looking for

perhaps creating a query that is based on the case and expense tables only? sinc ethey are still linked together?

There might be but there's always potential for future problems if you don't get the schema design right before you start writing the queries, forms and reports. In the real world a large number of people that take my advanced Access course are jumping through hoops to try to overcome design errors in their database - they've often left it too late to do anything about it. If that is your situation too there's nearly always a work-around but sometimes you will sacrifice the integrity of the database. I noticed that none of your existing relationships are constrained... again that's crucial too if the data is mission critical.

 

[rainman89]

Its never too late to start fresh..... but that was what i was afraid of.. i already have a huge chunk of this db completed... the queries for the reports were the last thing i was working on when i came into this problem.. still not sure what i am going to do about it. there is a lot more i would like to implement so i may be able to change some things here and there.... but a whole table restructuring would throw everything that i have done so far for a loop... decisions decisions

 

[The_Doc_Man]

rainman, your basic question is to look hard at your business model for "entities" that are significant to the model.

Let's digress for example only...

You are a person who makes items for craft fairs.

Your entities are finished products (sellable), customers, locations where fairs were/are to be held, raw materials, labor hours.

OK, change hats. You are a chef in a restaurant.

Your entities are recipes, raw food, spices, tableware, flatware, linens, staff, tables, cooking utensils, appliances, ...

What you need to do is make a list of the categories of things that are part of your business. These become your entities. Then you have to decide how you treat each entity and how it is tracked. That will go a long way towards deciding the answer to your question.

Now, let's go back and ask the question with that thought in mind.

i was trying to get around the fact that an expense needed to be related to a time entry... wanted them to be seperate.

This can happen if an expense exists that is NOT related to a time entry in the "real world" business model.

Here's how it happens in our office.

We have contracts to which time charges represent an expense. However, the people who work on these contracts sometimes have to buy supplies or take trips. The latter two categories are what we call "other direct costs." The expenses on any contract are then the SUM of the labor charges and the other direct charges.

How do you do this? Damfino - it's your DB. But you can do it many ways. Here is one way that comes to mind. Have TWO tables that represent charges. One is labor-related and for that table, ALL entries correspond to a time entry. It becomes possible to either (A) establish a hard-and-fast relationship from Labor Charges to Time Expended or (B) include the time charges IN THE SAME TABLE. (Matter of formatting.) For the other table, you have the "Other Direct" stuff. This will tie back, not to TIME charges, but to expence sheets, receipts, etc.

Now make a UNION query that stacks time charges with other charges so that you can use that query as the basis for billing information.

But that is only true if your shop is similar to the model I just explained for my shop. Which is also why you need to understand that you are going to have to be the one to figure this out. We don't know your model. All we can do at THIS stage of the game is help you with guidelines and approaches.

 

[rainman89]

Guidance is all i ask for.. not asking for u to make my decisions! and i appreciate every piece of info that u give me.. i really do!

I believe that the ideas u gave about how to structure my db are what i have... ie the 2 seperate tables that come in for the expense sheets, and that is how i would like to keep them. I believe our businesses are similar.. so what u said would apply

how then would i link the tables... if adding an employeeID to my expense table ruins my previously existing queries?

 

[rainman89]

after renaming tables and fields and adding a link table

 

[Mike Smart]

after renaming tables and fields and adding a link table

Dear Rainman

You've done a great job with the re-design, the schema is starting to look good.

The only things I'd change are: Employees and Tasks tables (name in the singular).

Now back to design issues.

One Case has many Expenses (good)
One Client has many Cases (good)

One Task may have many time entries related to it (good)
Every expense has a single expense type and payment method (good)

Now the bits I think you've got wrong:

Table EmployeeTimeExpense table isn't needed.

You told me earlier that a single time record was related to one and one only Expense record and vica versa (a one-to-one relationship). This means you should take all of the fields in the Time table and put them into the Expense table. They can have null values is the expense isn't time related.

Add EmployeeID to the Expense table as a foreign key
Add CaseID to the Expense Table

Constrain the relationships between Case-Client, Task-Time, Expense-Expense Type and Expense-Payment Method

I think you are now there! You have modelled:-

One case has many expenses
One expense has a Type, Payment Method and Task
One expense MAY HAVE time related elements
One expense is linked to one and only one Employee

Assuming that I have understood your business correctly you should now have a perfect schema ... or we can go for take three!

 

[The_Doc_Man]

Adding an employee ID messes you up only if you intend to use it as a foreign key that must be used in a query. Otherwise, it is merely static data.

If it messes something up, you did the addition wrong, ran out of room, or used a keyword as a field name, or in some other way didn't add the field correctly. There is also the circularity issue that was brought up, and there is where things get really hinky.

I'm not going to say you MUST do this, but with the warnings you got with circularity, you have to stop and think hard. In a real-world model, having circularity implies that expenses themselves drive some other part of your business that feeds back to expenses. And that just cannot be right.

 

[rainman89]

I thank you very much for your help!

 

[rainman89]

doc man,
yeah i realized that the circulatory problem was a major major downfall of my system and have since then redesigned based on help recieved from mike smart. i thank you, also, for all of your guidance