I am dead in the water

[REAbernathy]

Here is my question and I hope someone can help me and I hope this question make some kind of sense? I want to be able to allow as many people as possible (Assigned To) to enter data on the form but if they all enter data at the same time i want there data to be the current record for them and not everyone else. I guess what I am saying is if all employess are on the form at the same time and they hit a command button called Preview Report then they should see only there report and not the other employees report.



Secondly, I want to be able to take that information form the Preview Report to populate the TO: section of an email. Once the firm was picked (Customer Name) by the employee (Assigned To) whatever firm that was selected there email addres should populate with there correct email address (Firms Information Query) in the TO:section.

Again and I cant stress this anymore I am a NEWBIE to Access and this stuff may seem easy for some people however its proving to be a tad bit difficult for me at this time. I will attache a copy of my database and any assistance that you can provide will be very helpful. Thanking You in Advance....

[jzwp11]

In order for Access to know who the "Assigned TO" to person is, you need the person to login in and from there you can filter your forms and reports based on that person. Typically you would have a table to hold all of the assigned TO people.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-txtPassword


You would reference the pkPeopleID value in your tracking table rather than having the person's name there (no need to duplicate the name info)


TrackingSystem3
-ID
-fkPeopleID foreign key to tblPeople (replaces your AssignedTo field)

BTW, you should be referencing the key field for the error code in the tracking table as well not the text of the error code duplicated from the Error Codes & corrections table


TrackingSystem3
-ID
-fkPeopleID foreign key to tblPeople (replaces your AssignedTo field)
-fkCodeID foreign key to ID field of Error Codes and Corrections table

Just some general recommendations:

1. It is best not to have spaces or special characters in your table or field names
2. Do not use reserved words or symbols as table or field names (here is a list)
3. Do not use table level lookups in your tables; lookups are best left for forms. I did not see any in the tables I looked at in your database. This site explains why table level lookups are not a good idea.
4. It is best to have more descriptive field names for the primary key field in your tables rather than just ID. Having several fields called ID will cause confusion.

[REAbernathy]

Hello JZWPP11,

I have made the following changes to my database as you have advise me too. Also you asked me to create another table called TblPeople however I already have a table that is similar to that TrakAmericiaDSE:Table. I will attach a new copy of TrackingSystem33 for you to review and please tell me what you think. From there I am still a little confused but the little bulb is getting a little dim compared to being out a few days ago.

So with me making these changes will it allow the user to enter infromation into the form and when they click the Save Record button and then the Preview Report button will it just show there current report? Also will it allow them to just E-mail the current report that was just created by the representative and and not all of the reports.

[jzwp11]

So with me making these changes will it allow the user to enter infromation into the form and when they click the Save Record button and then the Preview Report button will it just show there current report?

You will need a login form for the users to login. Once you know who is logged in you can filter the form or report to just show the records pertinent to that person.

BTW, I did not see an attachment.

[REAbernathy]

Here is a copy of my updated database.

[jzwp11]

Which of the tracking tables is the correct table: trackingsystem3 or copy of trackingsystem3 ?

[jzwp11]

I was looking at your database in more detail. You do not have any relationships set up. Also, you repeat the firm information in the firm table because you have multiple contacts listed. If a firm has multiple contacts that describes a one-to-many relationship which by normalization rules requires the contacts to be in a separate but related table. Furthermore, the contacts are people as well as your employees. I would typically have all people in one table.

Could you provide some more detail on what business process you are trying to model with this database?

[REAbernathy]

TrackingSystem33 is the correct tables. I am trying to create a Tracking System Database which will allow the Representative to input information about their phone or e-mail conversation with the client. Also I want to be able to allow the Representative to send a copy of the report to the client via e-mail and advise the firm on the corrections that need to be made. And furthermore i want to be able to send a current copy of the report via e-mail to the correct client. Will send you another verison of the TrackingSystem33

[REAbernathy]

Here is the newer version of TrackingSystem33

[jzwp11]

When a tracking item is entered, does it pertain to the firm itself or a particular contact at the firm?

The error code/description info in the tracking table. Is it suppose to refer to the error code table? What is the tiger table all about? It looks a lot like the error code table.

[REAbernathy]

It pertain to a particular firm however in that firms they may have several contacts they review the printed information in order to get rid of there errors. The Tiger Table is one type of Error Code that a firm my have and then the Error Code/Decription gives you the error code and the correction on how to fix the error code. So in short one gives the Error Code the other gives the Error Code and also tells you how to fix it.

[jzwp11]

It pertain to a particular firm however in that firms they may have several contacts they review the printed information in order to get rid of there errors.

Can multiple contacts at the firm be associated with 1 tracking item?

Is there always an error code associated with a tracking item?

Can a tracking item involve multiple error codes?

The Tiger Table is one type of Error Code that a firm my have and then the Error Code/Decription gives you the error code and the correction on how to fix the error code. So in short one gives the Error Code the other gives the Error Code and also tells you how to fix it.

All error codes should be in one table. So some error codes may have a corresponding tiger error code and associated error description, correct? All error codes, no matter what type should have 1 (and only one) corresponding correction, correct?

[jzwp11]

BTW, do you have multiple events associated with a tracking item?

Based on what little I know about your application, I would structure the database as shown in the attached DB. Please take a look at the relationship window.

[REAbernathy]

Please review this and tell me what you think. After these corrections have been made will it allow me to determine what record is the most current record for the Representative that is entering the data? Secondly, will it also allow me the chance to populate the email field automatically? I have been working with this for sometime and I need to give my boss something that works halfway and then bide me some more time to correct the other issues. I really appreciate all that you have done so far and I am indebted to you. Thanking You in Advance for Helping a Access Newbie like myself.

[jzwp11]

The database you posted does not look much different than the previous versions you have posted especially with respect to the table structure.

As to finding the most current record for an employee. That can easily be handled with a query but again it depends on having the correct table structure. You current data for the employee field in the tracking table is inconsistent. Some records have the first name while others have the last name. In order to return the current record for each employee, the names must be consistent either first or last but not both. That is a primary reason why you use the key field as the foreign key to link tables. The key fields generally have no significance to the user whereas the first/last name fields have significance.

will it also allow me the chance to populate the email field automatically

In terms of the e-mail address, yes you can populate either the employee's or the contact's e-mail that would take some integration with Microsoft Outlook but it is definitely possible. You are not at that point in your design to even worry about that yet. You must fix the table structure first.

You have not addressed the following questions I posed :

Can multiple contacts at the firm be associated with 1 tracking item?

Is there always an error code associated with a tracking item?

Can a tracking item involve multiple error codes?