I am dead in the water

[REAbernathy]

I am sorry you are correct i didnt answer your question. And i sent you the wrong DB by mistake. I will send you another one that shows the corrections.



1) Yes multiple contacts at a firm can be associated with 1 tracking item if I am understanding your question correctly? For example suppose your firm JZWP11 had several e-mail address i want all of those e-mail address that are related to that firm applied for that particular issue. the reason why multiple e-mail address are used is due to the fact that some firms have ceritian employees correcting only specific error codes.


2) Yes there is always and error code associated with a tracking item. For every error code there is a descripition code that belong to it as well. I have only added some of the Error Codes and Error Descripition just for testing purpose. I will add the rest when i get everything working correctly


3) Yes some firms have multiple error codes so on the form I gave the user the option to choose multiple Error Codes and multiple error codes and descripition to satify what needs to be done.

[jzwp11]

the reason why multiple e-mail address are used is due to the fact that some firms have ceritian employees correcting only specific error codes.

Based on the above, it sounds like you have a relationship between the contact person at a firm and the error codes that they handle. It sounds like a contact may be responsible for multiple error codes, so you would need a table to make that association

tblContactErrorCodes
-pkContactECID primary key, autonumber
-fkContactID foreign key to a table that holds all contact people
-fkErrorCodeID foreign key to a table that holds all error codes

For every error code there is a descripition code that belong to it as well

This suggests the the error codes, the description code should all be in one table (different fields of course). You mentioned something about resolutions that are applicable to a code. If there is only 1 resolution for each code then it would go in the code table. If there are many resolutions or steps to resolve 1 error, then you would need a related table to hold those resolutions.

Yes some firms have multiple error codes so on the form I gave the user the option to choose multiple Error Codes and multiple error codes and descripition to satify what needs to be done.

If a tracking item can have many error codes, that describes a one (item)-to-many (codes) relationship. To properly handle that, the codes related to a tracking item MUST be in a separate but related table not in the tracking table.

tblTrackingItemCodes
-pkTrackingItemCodeID primary key, autonumber
-fkTrackingItemID foreign key to your tracking table
-fkErrorCodeID foreign key to your table that holds all error codes

[REAbernathy]

New DB with corrections

[jzwp11]

Looking at the relationship window, you have several problems. You have a join between the TrakAmericiaDSE table and the trackingsystem3 table via the employeeID field which is correct but then you also have another relationship between the first name field of the TrakAmericiaDSE table and the opened date field of trackingsystem3. That does not make any sense.

You also have a join between the firm table and the trackingsystem3 table but you have both the firmnumber and firmname fields of the firm table joining to the firm number field of the trackingsystem3 table. Trying to join the 2 fields to 1 does not make any sense.

Then you also are joining the error codes table 3 times to the tigers table. That makes no sense either.

Please take a look at the relationship diagram in the database I posted earlier (post #13).

[REAbernathy]

I think i gave you the wrong information. The Tiger Table gives you the Error Code and Description of the Error Code..ie 1007-Error Code ; Invalid Account-Error Description.

Error Code and Corrections Table gives you the Error Code....1007; And then the Correction-give the Error Code again and the correction to the error code.

[jzwp11]

...give the Error Code again

All of the info should be in 1 table based on what you have described.

[REAbernathy]

Dear JZWP11,

I think I have finally gotten it write. Please tell me what you think and if i need to make anymore corrections i will do that as well. I have attached a copy of my DB again with the corrections. And again Thank You for all of your assistance in this matter.

[jzwp11]

In general, I use the key fields to make the joins between tables since relational databases operate more efficiently with number fields rather than text fields. For example, you are joining the status table to the firm table via the text field (status) rather than numeric key field. Joining by way of the text field is OK but the database would be more efficient with numeric fields.

Also when you make joins between two tables, the two fields that make up the join have to be the same data type. For the error description/correction fields in the tracking table, you have them set up as memo fields but the corresponding fields in the error code/tiger tables are text fields. I still think you need to move the corrections to the tiger table and get rid of the error table. Having two separate tables is causing you more trouble. A simple update query could do the job quickly (after you add a field for the correction text in the tiger table). The update query would look like this:

UPDATE Tigers INNER JOIN [Error Codes and Corrections] ON Tigers.[Error Code] = [Error Codes and Corrections].[Error Codes and Corrections] SET ErrorCorrection = [Error Codes and Corrections].[Corrections];


Also, it is best not to use the colon in your table names.

[REAbernathy]

Added the field in Tiger Table as Corrections with a Data Type of Text however I am still getting an error messaage Type Mismatch in Expression also when i had error description/correction fields in the tracking table set up as text none of the information was importing correctly however if i need to change them to text then that's what i will do.

[jzwp11]

I am still getting an error messaage Type Mismatch in Expression also when i had error

When & where are you getting this message?

if i need to change them to text then that's what i will do

Setting them up as text would be best. I do not believe that Access can make joins between memo fields in queries which would really cause you problems.

[REAbernathy]

I am getting the error when i try to run the query.

[REAbernathy]

Here is a newer version of my database. Now when i run the query i get a Mismatch Type

[jzwp11]

All of the queries in the database you provided ran fine. Which query was giving the error?

[REAbernathy]

The query that you gave me in #23

UPDATE Tigers INNER JOIN [Error Codes and Corrections] ON Tigers.[Error Code] = [Error Codes and Corrections].[Error Codes and Corrections] SET ErrorCorrection = [Error Codes and Corrections].[Corrections];

[jzwp11]

Are the data types for the fields Tigers.[Error Code] and [Error Codes and Corrections].[Error Codes and Corrections] the same? What datatype did you set up for new field in the Tigers table to accept the correction information? What is the data type of the corrections field in the Error Codes and Corrections table (they have to be the same as well)?