Using a column name in a query (1 Viewer)

[Christopherusly]

I have an online timesheet system - its basic but does what i need - every week i pull out a CSV file with everyones time and then make a report.

In order to expedite the reporting process i thought I know, lets make a database with a nice report already formatted. So far so good.

HOWEVER the data structure in the CSV file is not conducive to easy reporting in a database as the person name is a column title, not part of a row entry for the timesheet.

So my question: Can you use a column name in a query ...

i.e. i want a drop down list of names that i can select from, which passes the selected name to a query which pulls it from a column name in the time sheets table, then returns all the values from that column where it is greater than 0.

Thanks you guys and nice to be back again.

 

[Galaxiom]

Can you use a column name in a query ...

It is possible using VBA but it is very clumsy. The real problem is the data structure.

You really need to convert the data into a normalized structure so that the employee time data is held in individual records. One record for each employee and date.

Then the reporting will be very simple.

 

[Christopherusly]

I know, but its a team of 20 engineers with on average 4-500 time sheet entries, there seems no way to normalize it from the system so i would have to do it manually, which would take even longer than i currently spend writing the reports.

I wonder if this has been done on purpose by the vendor as there is an add on for reporting which costs over $1000 damn them and their clever ways.

Thanks for your reply tho ! it is appreciated

 

[Galaxiom]

Although the VBA could be written to generate the report queries it would be better to write the VBA to normalize the data.

This is done by looping through the field collection of the imported csv and generating a query to append the data from each field into a normalized table structure.

How much knowledge do you have of VBA?

 

[Christopherusly]

reading VBA for access dummies as we speak !

 

[Galaxiom]

It is a fairly simple loop for someone who knows how. If you post a sample of the csv with a few dummy records I can write the normalizing code for you.

Sounds like you need a bit of a break on this one.