beginner's problem

[visitnag]

Hi,

I have the following tables:

A master table of employees with Name and employee ID

One table with employee IDs as rows and GIFT names as columns (10 columns)


another table with employee ID as rows and GIFT names as columns (3 columns)

The gift columns contain the the number of gifts got by each employee.

I want to build a query on employee that the result should show how many gifts the employee
got from each table. This should also move the row from the GIFTs tables and append to a new table.

The GIFTs columns are different for each table. If they are not movable to another table at least there should
be a column posting that the GIFTs are distributed to queried employee

Thank you.

[CarlettoFed]

Sorry what does GIFT mean? and you should attach an example file, with a minimum of data replacing possibly sensitive ones, to see the structure of the database.

[June7]

This is not a normalized data structure. This is like a spreadsheet. Instead of columns (fields), each gift should be a record. Why do you have two gift tables?

Would have to add fields and if field could be Null, have to deal with that: Nz(Field1) + Nz(Field2) + ... + Nz(Field10).

This calculated value should not be saved to table, just calculate when needed.

Have you studied an introductory book or any tutorials on relational databases?

[alansidman]

Here is a very good white paper on data structure in a RDBMS

[visitnag]

You are right. Its a spread sheet. The different GIFT spread sheets are for two different competitions. I am very much new to access.
Thank you

[CarlettoFed]

Unfortunately inside the .doc file none of the images are displayed.

[June7]

Images show for me.

[Micron]

They are there when I open it.

[CarlettoFed]

The images occupy the space but I see nothing.

[June7]

Don't know why. I see images just fine.

[Welshgasman]

Unfortunately inside the .doc file none of the images are displayed.

They do take a while to load.?

Is this any better?

[ArviLaanemets]

Based on all posts here you need tables like:
tblEmployees: EmployeeID, FirstName, LastName, ...;
tblCompetitions: CompetitionID, CompetitionName, ...;
Optionally (in case same gifts can be given in different competitions, or to different employees) tblGifts: GiftID, GiftName, ...;
And finally, either (in case you have tblGifts, you link to it using GiftID, otherwise you enter GiftName directly into tables below):
tblCompetitionGifts: CompetitionGiftID, CompetitionID, GiftID/GiftName;
tblCompetitionEmployeeGifts: CompetitionEmployeeGiftID, CompetitionGiftID, PersonID.
or:
tblCompetitionEmployeeGifts: CompetitionEmployeeGiftID, CompetitionID, EmployeeID, GiftID/GiftName, PersonID.