Can a Report be created under the follingconditions?

[djclntn]

I have this database which tracks my daily tasks on the multiple projects I’m actively working on. My Table has common field such as, the date, Project Name (or Project ID), the File name, Hours (Hrs), DateCompleteted, etc. One common theme which is important is, ALL of my File Names starts with a year, Eg. “2000” Now, what I wish to create a Report based on 1-particular Project & see the total amount of Hrs & Billing according to ALL of the File Names according to a given year. I’ll show an example in a minute. But first, I thought that the easiest solution is to create another field which just has the year – the File Name. eg. If the File name is entitled, “2011 Driver’s Standings” just put “2011” in a field name, “Years” & then I could sort by that field. I want to avoid taking this route because I find it clearly redundant. Also & more importantly, from my daily tasks projects database, which certain projects I’ve created other databases which are linked to my main daily tasks Projects dbs. So, one of my Projects is NASCAR which I have many File Names & daily tasks entries.

So, say I have the following list of File Names:
2004 Drivers Standings
2004 Series Results
2004 Series Totals
2005 Drivers Standings
2005 Series Results
2005 Series Totals
2006 Drivers Standings
2006 Series Results
2006 Series Totals
2007 Drivers Standings
2007 Series Results
2007 Series Totals
2008 Drivers Standings
2008 Series Results
2008 Series Totals
2009 Drivers Standings
2009 Series Results
2009 Series Totals

I want to know if/how in a Report I can get the totals grouped according to the “Year.” Eg. All of the File names from the year 2008.

[ssanfu]

Your terminology is very confusing. You keep using " the File Name" & field name. Is that a file outside of Access or a table name?

If "2004 Drivers Standings, 2004 Series Results & 2004 Series Totals" are table names, then you do not have a normalized structure. (So each year, do you have to recreate your forms, queries and reports?)

If they are table names, then yes, you have to add another field for the year to each table, then you could use a union query to get all the tables into a recordset and group/sort on/by the year.. A real PITA, but necessary when the database structure is not normalized.

Would you provide more info on your db design?

[djclntn]

When I refer to a "File Name" I mean that it’s the name of an outside application may it be an Access, Excel, Word, etc. file. For instance, I have files for (2003 Drivers Standings) in an Access, Excel, & in a Powerpoint file. Within this DailyTasks Table I also have a field name entitled, "Applications" in which I assign an application (Access, Excel, Word, etc.) accordingly to the outside File Name. Thus, this is one of the reasons why I have the same File Name in multiple records within the DailyTasks table because A) one File Name, (2003 Drivers Standings) can be worked on more than just one day & B) (2003 Drivers Standings) can be used in multiple applications. Plus if you look back at my original post, for each year starting from 2000 I have more the one file name starting with the same year, but have a differ name following (eg, 2003 Drivers Standings & 2003 Series Results"

Probably, the smartest & my best bet to do is to create a field which just listing the "Year" according to the File Name. However, that would mean a lot of work & reconstructing, which I can’t see myself doing. Besides "NASCAR" as one of my Projects I keep track of other projects (sports related) where the file names (the names of a file in another application/separate from my DailyTasks Database) are similarily set up the same. Eg. 2003 Standings (project ID NFL) -or- 2003 Standings (Project ID NHL).

Granted, I may have confused you even furthered vs. make myself clearer. If so, then I’m just bag this whole thing & I thank you for your time!

Dave

[ssanfu]

Dave,

I think I understand...still a little confusing.

Since the data will be in several different file types (Access, Excel, word, txt, csv), you would still need a field to know the year of the data. Unless you imported the data from each file and added a field for the year, I can't see a way to make a summary report.

Maybe ..... you would need to write VBA code to link the different files (if not already linked). a series of make table queries, update queries to add the year of the data, then a union query with all of the temp tables, then a totals query from the union query.

Whew..... a lot of code to write.... but theoretically possible.

Since you have 3 "types" of files/data, presumably with different fields, you would need 3 sets of code (subroutines) just for the "NASCAR" files. And the same for NFL and NHL.

Lots and lots of code......

[djclntn]

Steve,

I was ready to give up except a few things of what you wrote alarms me that I’m not being clear enough to you.

<Since the data will be in several different file types (Access, Excel, word, txt, csv), you would still need a field to know the year of the data>

I’m not sure what you mean by this statement?

Not to waste anymore of your time, writing VBA Codes is totally out of my range of knowledge & don’t know even how to begin writing any type of VBA Codes. With said, I’m going to send an Excel Worksheet as an example of my DalyTasks Table with a few notations. Of course, it’ll be to your discretion if you want to continue this challenge.

Dave