Access Table

[cassie123]

Hi Everyone,

I hope you guys can help me with this as I am beginner in Access.

I have a list of over 100 employees name and have multiples name under each name. I like to see a table that show one employee name and list out all items that related to the name.

Please help and thank you so much.

 

[cassie123]

multiple items*

 

[Joe4]

I have a list of over 100 employees name and have multiples name under each name.

What do you mean by this?
How exactly are you data tables structured?

You should probably have at least two or three tables:
- Employees: List each employee exactly once. Should have a unique Employee ID field.
- Items: List each kind of item exactly once. Should have a unique Item ID field.
- Employee/Items: Lists all the items associated with each employee. There should be a separate record for each Employee/Item combination. Should have two key fields, Employee ID, and Item ID, which are used to link these records to the other tables.

So then, you could either do the following to get what you want:
- Query: Link your Employee and Employee/Items table
- Form: Base your Form on Employee, do a Subform on Employee/Item
- Report: Base your Report on Employee, do a Subreport on Employee/Item

 

[cassie123]

For example, I have parking, meals, holidays parties, phone charges under Adam.

However, when I was trying to put Primary key on employees first name, it says error as null values. What can I do here. I will try on the way that you mentioned above

 

[Joe4]

Your primary key must have the following characteristics:
- Must be populated for ALL records (cannot be null or blank)
- Must be unique

If you do not have an inherent field which meets these properties, you will have to create one. Autonumber is often used, as it is guaranteed to be unique. The caveat is that it is just a random number, so has no real correlation to the data it represents.

 

[Micron]

Joe4 is correct, but to elaborate:
tblEmpl- first field probably employee number (I've never seen a situation where 2 different people have the same ID number) so this is the primary key (PK) in this table and a FK (foreign key) in next table.
rest of fields; birthdate, phone, house/apt#, street, state, zip/postal, etc. (no volatile info such as age or yrs of service)

tblExpenses - first field is emplID from tblEmpl and is indexed, no dupes (not set to primary type) thus is the FK (foreign key)
rest of fields; expense types (as you have listed) OR
to be more normalized, you would have also have tblExpenseType with a PK such as an autonumber & each field is the expense type (cell, parking, etc.). In this case, tblExpenses would show:

EMPL_ID	EXP_TYPE	AMOUNT
122	1	80
457	5	45
552	3	12
123	3	75
174	1	100

<tbody>
</tbody>

Note that there are no spaces or special characters in the field names (nor should there be any in any db object name). The advantage to this level of normalization is, if you change an expense type name in that table, it does not affect anything else because your queries pass the name values of fields to your db forms. To add a new expense type, you simply add it to the expense type table instead of the expenses table. Adding fields to this table would muck up your queries, thus your forms/reports also. Info should be presented to or modified by users only via forms and reports, or locked datasheets at least.

 

[cassie123]

Before I create query, do I have to create the relationships between these table?

 

[Joe4]

Before I create query, do I have to create the relationships between these table?

No. You can do it right in the query.