Query question

[Antanas_07]

Could not solve a probably a very easy task. Looks like simple situation: table1: [PersonName], [Time spent on activity], [Record date]
table2: [ActivityName]
I would like to build a table (and this is a task)
Column1 - [PersonName] and names down (Name1, 2...N];
Column2 - [ActivityName1] and hours spent by each Person on Activity1 down
.........
ColumnN - [ActivityNameN] and hours spent by each Person on ActivityN down
This is in a time frame which is not an essence, I can set the time frame. But I am lost in sorting Time spent by Persons in different Activities. It is like you have to jump from 2D data in to 3D. :banghead:
I would much appreciate any help.

 

[pr2-eugin]

Your table 1 should have another column called ActivityID or something that will denote what activity the Person is doing, so you can categorize them using the CrossTab Query.

 

[Antanas_07]

Table1: Needs correction in the post. It is not clear. Table1 ActivityName1 and below the hours spent; Activity2 and below the hours. ActivityName column heading. I have 14 activities and 5 persons. Each person records a table - Table1. Date on which Activities performed and hours against every activity

 

[Antanas_07]

There could be idea of structure of all data collecting corrected or rewritten just for that purpose, but I can not see how to fit column of Activities in the Table1, unless change the table.
Thanks for replay by the way.

 

[pr2-eugin]

Please show some sample data and the result you wish to obtain. It would be great if your sample looked like THIS. To do this you might need to use the CODE tag in the Advanced Reply option.

 

[Antanas_07]

tblPersonName1 (Design view): Same tblPerson2, and so on
ActivityDate Date -date format
Activity1 Number - hours User select nr of hours
Activity2 Number - hours
Activity3 Number - hours

The structure is very simple
What I need: Table (Normal view)
PersonName Activity1 Activity2 Activity3
Person1 hours hours hours
Person2 hours hours hours
Person3 hours hours Hours

 

[pr2-eugin]

You mean to say you have table for every person? Why????? You should only have one table with one column denoting the person, the next column denoting the activity. This structure is going to be a real pain. If the structure is designed as I have shown then CrossTab query is just a breeze.

 

[Antanas_07]

At the time the idea looked well and it did well generating reports needed. But I had a bad feeling from the beginning. I am considering a new table based on advice. So what will crosstab will give me? I am new to this future.

 

[pr2-eugin]

Once you design your table the way I mentioned, the cross tab query will give you exactly the one you want. Check out : http://office.microsoft.com/en-gb/a...ad-by-using-a-crosstab-query-HA010229577.aspx

 

[Antanas_07]

I already seen what it does on a my sample. Can not deny the results. I have to sleep on perfection a bit but I see where all it heading.

 

[Antanas_07]

I can copy the recordsets in to a table during data enter. Will not be a problem. Love the outcome. Thanks for the advice.

 

[Antanas_07]

Still 5 persons 14 records a day = 90 records a day. 90*365 = 32850 records a year. would it be a concern for 1 table

 

[pr2-eugin]

I have more than 100,000+ records and still counting in my table.

It should not be a problem, as long as you are storing information correctly it will not pose a threat.