Query to compile dates from multiple fields 2

[RobNauticus]

Hello all,

I have a table that I want to report on. It contains several date fields (Tasks Completed). I want to write a report that will have the dates consolidated into one master field that will be the first group on the report.

Any suggestions on how to do this? I tried an append query targeting a new table that has one master date field but it would not let me do that.

Thanks,
Rob

 

[SkipVought]

Hi,

You only mentioned ONE date field (Tasks Completed) but stated that you had, "... several date fields ..."

How do you want to "consolidate" these dates? The MIN date within the group, MAX.

You also stated that you wanted to REPORT from this table, but then you also stated that you tried to APPEND. What is your business objective?

It might be helpful if you posted some sample data from your table and what you would like the report to look like.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[RobNauticus]

Ok I have multiple date fields representing when a task was complete (28 Date Fields). I need to produce a report with the grouping being on a consolidation of all dates from all the fields. I want the report to show only dates that have been entered into the DB.

I tried to append the dates to another table to consolidate them.

Then I want to create a report that will show the date as the first group then show all the tasks completed on that date.

Does that make more sense?

Thanks,
Rob

 

[SkipVought]

No, it makes no sense at all. 28 different date fields that need to be consolidated???

"Inconceivable!"

Where is that sample data I requested?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[GingerR]

Sounds like your table design is not normalized. This will cause you problems forever.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SkipVought]

In the word of Vizzini, "Inconceivable!"

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Have a look here:

http://r937.com/relational.html

You may have to play with a normalization union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobNauticus]

My First Table Contains Job Info

JDE Division Code(number)PK
JDE Number(text)PK
Master Plan(text)
Community(text)
City(text)
# Homesites(number)

This is the Second Table that has each task per job broken down.

JDE Division Code (Number)PK
JDE Number (TXT)PK
Site Prep (DATE)
Environmental Treatment(DATE)
Foundation(DATE)
Vertical Construction(DATE)
Specialty Items(DATE)
Plumbing(DATE)
HVAC(DATE)
Electrical(DATE)
Fireplace(DATE)
Windows	(DATE)
Exterior Finish(DATE)
Insulation(DATE)
Roofing	Wallboard(DATE)
Doors, Trim & Hardware(DATE)
Painting(DATE)
Iron Works(DATE)
Garage Door(DATE)
Cabinets(DATE)
Countertops(DATE)
Bath Surrounds(DATE)
Appliances(DATE)
Bath Accessories(DATE)
Decorator Items	(DATE)
Flooring	(DATE)
Flatwork	(DATE)
Landscape	(DATE)
Cleaning	(DATE)

I need a report that groups all the (date) tasks from the second table to show sometihng like this:

10/01/05
	Community1	Painting
	Community2	Cleaning
	Community3	Landscape
09/29/05
	Community6	Painting
	Community4	Windows
	Community3	Plumbing

Are we getting warmer yet?

Let me know if I need to clarify further.

Also thanks PHV I will check it out...

Rob

 

[SkipVought]

Rob

The problem with your table design is that it is not normalized and WILL continue to plague you.

What happens if you have a category for which yo have mo column???

Your table ought to have a column for Category (Windows, Plumbing etc) and ONE column for date.

Select [JDE Division Code],[JDE Number],'Site Prep',[Site Prep] 
From JobTask (or whatever you table is called)
Where [Site Prep] Is Not Null
UNION
.... and one similar select for eath OTHER date field

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[RobNauticus]

Thanks a lot Skip, I will restructure it.

 

[RobNauticus]

What will I do to enter the data then? I want to have users be able to enter in tasks as they complete and be able to change them if needed. What would the best approach be to do that in a form?

Thanks,
R

 

[SkipVought]

Have the tasks selected from a combobox

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[RobNauticus]

I know that much, but with the new format, how is a user to see all the tasks for a particular job in a convenient manner while they are doing entry?

I will try it out...

Thanks again

 

[GingerR]

You'd make a second table, that holds just the Tasks:

Table Name: Tasks
Field: TaskID (Autonumber)
Field: Task (Text)

Sample data:

1 Plumbing
2 Painting


Your third table would be like this (replaces your 2nd table listed above):

Table Name: JobTasks
Field: JobID (Number, from first table)
Field: TaskID (Number, from second table)
Field: TaskDate (Date)

Sample Data:

1 1 8/8/05
1 2 7/1/05


Then, for data entry:

You'd have a form, based on Job (or whatever you're calling it).
Then you'd have a subform, based on the 3rd table (JobTasks). This is where you're going to mate up tasks with jobs. On this form, you'd have a combo box based on the 2nd table (Tasks).

Read about many-to-many relationships and subforms.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[r937]

In the word of Vizzini, "Inconceivable!"

i love that movie!!


"when the mutton is nice and lean..."



r937.com | rudy.ca