Link to home
Start Free TrialLog in
Avatar of TCHGirl
TCHGirl

asked on

Combine multiple date fields

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.
Avatar of crundle32
crundle32

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

Avatar of PortletPaul
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)
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).
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
mlmcc,

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

 James
Thanks for catching that.  I guess I saw the tag.
Crystal Reports topic added

mlmcc
TA
Avatar of TCHGirl

ASKER

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
What are you trying to show from the report on these tables?

What do the fields represent?

mlmcc
Avatar of TCHGirl

ASKER

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#.
Avatar of TCHGirl

ASKER

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.
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}
Avatar of TCHGirl

ASKER

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?
'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)
Avatar of TCHGirl

ASKER

yes, I can read directly from SQL Server into CR.
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
Avatar of TCHGirl

ASKER

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.
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
>> "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?
Avatar of TCHGirl

ASKER

Where is this code suppose to go.  Is it a stored Procedure in Sql server or does it go into crystal reports?
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
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
Avatar of TCHGirl

ASKER

That is possible to have clients go to the computer lab and not go to class.
Avatar of TCHGirl

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial