Problem with table structure :( Help me out

[Unicon]

Hi I am recently using attendance in ms access and my table structure is like (Please find the attachment) I want to create a separate table where I can store different amount for different designation (which I can calculate later on basis of attendance) but here the problem is even for same designation I have a different salary depends on site. I think a lot how to create that one but I am not able to find out the solution. Please help me out
Your feedback on this matter should be highly appreciated .

 

[Mr. B]

Create a table for sites and provide fields to hold any information needed about one site, including the rate for that site. If these rates change from time to time, you may need to create a table for recording the rate for one site with an effective date for the rate and linke that table to the table of sites.

Create a table for designations and include only fields related to a designation.

Be sure to include a record ID field (autonumber) in each table, including your current table. You can use a field name like: SiteID for the AutoNumber ID field in the Site table. Do likewise in the Designations table.

Change your Site field and Designation fields in your current table to Number (long integer type) value. You might want to change the names of these field to something like "SiteID" and "DesignationID". Link the new tables using the record id fields in the new tables to the appropriate number field in your current table.

You can then design a form that will have your current table as its record source. In this form you can create records as necessary to define the attendance. You can use combo boxes that let you choose the correct site and another that will let you select the correct Designation.

 

[Unicon]

Mr. B,

Thanks a lot for the information will try in that way and will come back to you with outcome.

 

[Unicon]

Mr. B,

Please find the attachment and advice, I create a table in that way. Is it fine or need to modify some more?

 

[Mr. B]

You did not provide the names you have given to your tables. I hope you are not using any spaces or special characters in the names of these objects.

The first thing that I noticed from the attached graphic is, you need to be more speific about naming your ID fields. In each table identify the ID field in a way that when you see it later, you will not have to wonder from which table it is comming from. Right now you only have ID as the name of the field. I would suggest that in your Attendance table you would name the ID field to AttendanceID. You can use your imagination for the rest of them.

You have your primary keys wrong in the Attendance table. Make the ID field the primary key. The Site and Destination fields are know as "foreign fields" as they are the link to your other table. When you change the naming of your ID field as described above, you will not have field naming issues and will be able to use the same field names for the foreign key fields in the attendance table as you have named them in each of your other tables.

I am curious, what is the "Attendance" field in the Attendance table. I see that you have it as a number field?

Other than the issues I have discussed, your tables look like the will work.

Good luck with your project.

 

[Unicon]

Thanks again Mr. B I given that primary key in id no. and in date to avoid duplication for same id on same date as I have more than 1000 staffs to entered an attendance a day. And that attendance field is I have a separate table (code for the attendance like N, R, OT, S etc) I connect that field with that code table but still some more confusion are there like how can I see all staffs who work in different contracts with different salary for the whole month with calculation. Is there anything where I can see all people together with calculations for whole month? Please advice.

 

[Mr. B]

You would not need the date to be part of the Primary key. The AutoNumber field takes care of that as it will always be unique (no duplicates).

You will need to create a query to pull everything together. In the query is where you will apply criteria to filter for the type of information you want to see.

 

[Unicon]

Mr. B,

My structure is like this please find the attachment. How can I put it together for report (as attached excel sheet)?
Please advice.

 

[Mr. B]

In most cases, you have not named your primary keys with meaningful names. I have changes these names in the attached Db.

The most concerning table for me is the Employee table. You must have an autonumber field in this table also, so I have added this field. You have a field that is called Employee ID number and defined as a text type field. I am going to assume that this field is really designed to hold a value like a badge number or some other company assigned value. I have renamed this field so it does not have any spaces. This field is now named "EmployeeNumber".

One other thing in the Employee table: I would split the employee name into two fields, but that is just me. You can leave this as a single field if you like.

There were problems in the Attendance table. The names of the fields need to reference the field in the related table. Look at the changes I have made to this table, including the "Descriptions" for each field. Also, in the Attendance table, never define a look at the table level as this can cause problems later on. I am aware that Microsoft designed this capability into Access, but that does not preclude the fact that it can and will cause problems. (Do a Google on this subject.) You should define your lookups in your forms. I suspect that the "Attendance Number" field should really be designed to identify the shift type and be linked to the Code table. I have set it up this way and if this is not correct, you can change it.

You would be wise to use a naming convention to name your tables. I use a prefix of "tbl" in each table name. Believe it or not there are plenty of times you are very glad you use this type of naming. I makes it much easier to distinguish between a table and some other type object.

In the attached database file, I have made the corrections that I would maked if it were mine, including renaming the tables.

Take a look at the Relationships. I have defined the relationships between the tables.

The revised database is attached.

With this structure you should be able to get your reports the way you want them.

 

[Unicon]

Mr. B,

Thanks a lot for everything. I will follow the same in future. But still no idea how to create that report??? I am assuming that I need to create crosstab query for the same and I tried it too but unable to create. Please advice if you have something for that.

 

[Mr. B]

I would think that you need to get some kind of user interface (form(s)) created so you can do data input before you try to do reports.

You have to get "good" data in before you can get "good" reports out!

 

[Unicon]

Mr. B,

Thanks again but no idea how to create that one. Is it possible I can create crosstab for the same??

 

[Mr. B]

I am wondering why you are so hung up on a crosstab query. I really don't see any need for a crosstab query in anything that you would need to do with your data just to get totals. Totals are normally produced by grouping and summing in a query.

The first think I would suggest you do, is look into how to create a form to enter data. Try a Google search. There some good souces out there. Like the one at:

http://tutorials.beginners.co.uk/using-a-form-with-ms-access.htm

Many more links will be available if you do a Google searce for "ms access how to create a form"

 

[Unicon]

Mr. B,

Thanks again. I will just try to create a query. Lets see how it will go.

 

[Mr. B]

Well, ok, do as you like, but I believe I suggested that you try creating a FORM and not a query.

Just one word of advise: If you do not know what to do next, when someone gives you a suggestion, you would be wise to try to follow their suggestion. You will get much farther with your learning curve in Access that way.

Good luck with your project.

 

[Unicon]

Mr. B,

First of all sorry from my side. I am following your suggestion only. I create a form too for my DB but the problem is no idea how to get those assigned amount (On basis of Designation) for those staffs worked on those sides on form.

 

[Unicon]

Mr. B,

I am sorry. I am following your suggestion only. I create a form too but still I didn't get an idea how to get those assigned amount on basis of Duty Site Table(Designation / Site wise) in the attendance. I lost myself totally.

 

[Mr. B]

After looking at your original post, it now appears that your database may not be designed correctly yet.

If you are needing to record the attendance of any one individual at one site and later be able to know the designation of each attendee at a location and have the cost related to the attendees then you are going to have to have a different table structure.

Because each individual could have a change in their designation, the designation of each attendee will need to be identified at the time of the one specific attendance. Then if that individual gets a different designation attendance records would not be dependent on a designation defined per employee.

I think you need to really spend a little more time designing your data structure.

 

[Mr. B]

Take a look at the modified version of your database. I have create a form as a starting point for you.

I think your data will work with this form like is is.

You will still need additional forms to manage the individual tables.

 

[Unicon]

Thanks again Mr. B,
I have create a FORM in that way only. As per your reply you said that I have to have a different table structure to get those details. Please advice what type of table structure I need to design? So that I can design it properly.
Have a nice day!