Solved

Combine multiple date fields

Posted on 2013-10-22
24
605 Views
Last Modified: 2013-10-29
In my SQL database I have three different table for hours.  Each table has a field called hours date.  What I would like to do is create a report in crystals report that will allow me to take all three of these tables and use the hours date of all 3 tables, but make one parameter instead of 3 separate parameters.

Not sure if this make any sense.
0
Comment
Question by:TCHGirl
  • 9
  • 7
  • 6
  • +2
24 Comments
 

Expert Comment

by:crundle32
ID: 39592987
what about using "Union"?


Select Hours FROM Table_1
Union
Select Hours FROM Table_2
Union
Select Hours FROM Table_3

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39592994
not a lot :)

can you share the table definitions?
some sample data?
and an expected result?

in particular I didn't get this bit: "Each table has a field called hours date"
is the field name [hours] and the data type of that field is date?

(and by the way what type of database? e.g. SQL Server? Oracle? and version if you know it)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39592999
before you adopt a union approach, please note it might need to be UNION ALL

(union by itself could distort the result)

But I don't really know what the expected result is (hint).
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39593077
If you are asking about how to use a parameter to select records from 3 tables.
You don't need 3 parameters unless you wantto compare to different values.

The parameter can be used in comarisons to all 3 fields.

If you want to use the UNION idea then all select statement must have the same number of fields.  The fields must be in the same order.

Do the tables have the same fields?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39593555
mlmcc,

 Since you're "here", you might want to add the CR zone.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39593886
Thanks for catching that.  I guess I saw the tag.
Crystal Reports topic added

mlmcc
TA
0
 

Author Comment

by:TCHGirl
ID: 39598935
Crystal Reports is a software that I have had to learn on my own.  My tables are located in SQL.  
Let's work with two table first.
Both table have a date in them.  What I want to do is merge these two tables together so I only get on total count.  In my computer table I have 495 records and in the education table I have 3275 records for the year 2012.  I know that when I combine tables there will be duplicates and that is ok, because when they are run in a report that will be grouped.  So how do I accomplish this in a crystal reports.

Computer Lab Table              Education Hours Table            
   TCHID# - number                      TCHID# - number
   Class_lab - text                          DateHours - date/time
   Lab_Date - date/time                 classhrs - number
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39598947
What are you trying to show from the report on these tables?

What do the fields represent?

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39598981
These fields represent clients who have come into our computer lab and have attended education classes.  So if I combine that tables together I should end up with 3770 records. Then I can set up the parameters that I need to so that I can then group the clients together by TCHID#.
0
 

Author Comment

by:TCHGirl
ID: 39599032
I don't want to use the union, I tried that in Access to see what would happen and I did not get the outcome that I was looking for.  What happened when I used the Union was that it combined both dates into one date and that is not what I want.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39599114
I don't think 3770 is necessarily the correct number of rows

e.g. Is it possible for no education hours to be recorded against a lab?
(perhaps the lab was being renovated or just not used for education)

but; I presume it's not possible for a record to exist in "EducationHours" that does not refer to a lab (well even if they did they wouldn't be relevant to this question)

With those assumptions are you able to run these 2 queries and provide the results please:

SELECT count(*)
FROM ComputerLab AS cl
INNER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

SELECT count(*)
FROM ComputerLab AS cl
LEFT OUTER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

The difference (if any) will indicate if records exist in labs that have no education hours against it. Note, if you are uninterested in this (i.e. your prime interest is only those labs with hours then you want to use the "inner join", but if you are interested in knowing which labs have no hours then you want to use the "left outer join").

I'll assume you need the "INNER JOIN", so a query that combines those 2 tables would be like this:
SELECT
     cl.TCHID#
   , cl.lass_lab
   , cl.Lab_Date
   , eh.DateHours
   , eh.classhrs
FROM ComputerLab AS cl
INNER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

Open in new window

I'm not strong on Crystal Reports (CR) so I'll let others deal with those details.

nb: the syntax used is for MS SQL Server, if the database is Oracle remove the word "as" after each table name. Oh, and the table names are a guess - you have to change those to the real names.

hint: it's always useful to tell us what the database type is

{+ edit, sorry, corrections}
0
 

Author Comment

by:TCHGirl
ID: 39599124
My tables are located in SQL.  I also had an idea of using Access to combine information into a new table and go from there.  Will this work?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39599137
'SQL' could mean just about anything, but I guess you mean Microsoft SQL Server

CR can read directly from your MS SQL Server database - is that correct?
(i.e. you have this permission/security access)

You can use Access if you need to - but is there a good reason to do so?
This does not appear to be a very complex need, and pushing data into Access to merge the data just seems like extra work (and probably makes things slower)
0
 

Author Comment

by:TCHGirl
ID: 39600513
yes, I can read directly from SQL Server into CR.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39601191
DO the LabDate and DateHours fields related?

Computer Lab Table              
   TCHID# - number                      
   Class_lab - text                          
   Lab_Date - date/time                

I assume the TCHID is a student id and the class lab is the class or lab being attended and the LabDate is the date and time of the lab.

Education Hours Table            
TCHID# - number
DateHours - date/time
classhrs - number

IN this one the TCHID is also the student id and DateHours is the datetime of attending and the classhrs is how long the session is or perhaps the credits

If that is the case it would seem that the tables are related through both the TCHID field and the corresponding datetime fields.

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39601561
The above is correct, the problem is that they are in two separete tables and I am trying to combine them into one table without losing any records.  There are other fields in both tables, but I only want the above fields to work with.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39601664
So if student 1 attends lab1 on 9 Sep 2013 at 11:00 AM
The Education Hours Table will show
Student 1, 9 Sep 11:00 AM 2 hours

mlmcc
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39601681
>> "I am trying to combine them into one table" ; do you mean

a query result?
a temp table?
or permanently change the data?

regarding this: "without losing any records"

have you run the 2 "select count(*)" queries suggested at http:#a39599114 ?
what were the results?
0
 

Author Comment

by:TCHGirl
ID: 39601714
Where is this code suppose to go.  Is it a stored Procedure in Sql server or does it go into crystal reports?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39601730
If you mean the 2 select count queries, you can run them in SQL Server Management Studio

You could use them one at a time as the SQL Command for a report but that seems like a bit of overkill

mlmcc
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39601766
those 2 count queries are going to help us determine how to avoid losing data
they are not the solution, I indicated the purpose as:

>>"The difference (if any) will indicate if records exist in labs that have no education hours against it. "

and the reason we need this is to decide if we use a LEFT OUTER JOIN or an INNER JOIN
0
 

Author Comment

by:TCHGirl
ID: 39606996
That is possible to have clients go to the computer lab and not go to class.
0
 

Author Comment

by:TCHGirl
ID: 39607073
Let's forget combining the tables together, because it looks like this is not going to work.  So here is what I would like to do instead.

In crystal reports I want to pull the dates from both tables, but only use one date parameter for both of these tables.  Is that possible?

I know that in Access my query I would use the "or", but how would I set this up in CR?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39607612
SAme basic way as in Access

{DateField1} = {?DateParameter}
OR
{DateField2} = {?DateParameter}

mlmcc
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now