Combine two fields to work as one 2

[amoeba102]

I am using CR9 Developer with two Access 2000 tables in one database.

I have a 'current' table and a 'history' table that each have a field named 'status' and a field named 'status_date'. On my report I need to work with these as though these fields were in one table.

My group#1 is SS# and Group #2 needs to be a paired status.

A paired status means a 'history.status'="E" must pair with either a 'history.status'="P" OR a 'current.Status'="P". The pairing is done by 'status_date', which means I must be able to sort by date as though 'current.status_date' and 'history.status_date' were in the same field (I think). There may be 0, 1 or many such pairings per ss#.

This seems as though it should be simple, but I'm missing it. Any help?

 

[FVTrainer]

It sounds like you need to bring your data from the two table into a single table. Are you familiar with UNION queries? Since you are using Access and CR 9 you have (at least) two choices for creating the UNION: 1) Create a UNION query in Access and use it as the datasource for your report, or 2) Use a Command object in Crystal as a datasource.

To create a Crystal Command object, open the Database Expert and click on Make New Connection. Using either ODBC or the Native Access driver, connect to your database. Then double-click on the "Add Command" option under your connection. The syntax for the UNION query is:

SELECT c.field1, c.field2, c.field3...
FROM current c
WHERE ...
UNION ALL
SELECT h.field1, h.field2, h.field3...
FROM history h
WHERE ....

If you haven't worked with UNION queries before, please note that the order and number of the fields must be the same in both SELECT statements.

 

[amoeba102]

Great idea, and I'll bet it would work if I knew what I was doing. You were right in your assumption that I've not used this feature before. I'm much more comfortable using Oracle than Access. Using the following command in the 'Add Command' section yields the error "Not Supported" Source: DAO.Database Syntax error in FROM clause."

select B.'Status', B.'Status_date'
from BGS-Data B
where B.'Status' = 'P'

Union All

select H.'status', H.'date'
from Status_History H
where H.'status' = 'E' or H.'status' = 'P'

 

[FVTrainer]

It would appear it doesn't like the "-" in your table name (I modeled the same table name a got a similar error). Without the "-" mine worked fine.

So, to remedy that, enclose the filename in "[]" as in:

from [BGS-Data] B

Also, you shouldn't need the single quotes around the field names (at least it didn't work on my system with the quotes around it). So, your query should look like:

select B.Status, B.Status_date
from [BGS-Data] B
where B.Status = 'P'

Union All

select H.status, H.date
from Status_History H
where H.status = 'E' or H.status = 'P'

It isn't obvious on how to edit the command object once you've created it. Just go back into the Database Expert and right-click on the Command object, then select Edit Command.

 

[amoeba102]

Now I remember why I don't use the "Add Command" feature. I tried it with much enthusiasm when CR9 first came out, and ran into the same types of problems.

I have entered the exact code you gave me, keystroke for keystroke. I double-checked all of the table/field names. Now I get the very informative error "Description: Too few parameters. Expected 1."

Is there any useable documentation on the "Add Command" syntax and explanations of their 'descriptions'. I'd really like to be able to use this. (As well as solve my immediate problem). I really appreciate your assistance. Maybe eventually we can get this to run..??

 

[FVTrainer]

Hmmm...I've had no significant problems with command objects, but I've been using them against SQL Server. Maybe a little more info will help: Which version of Access are you using, are the tables you are going against native Access tables or are they linked tables, and which driver (native or odbc) are you using?

 

[amoeba102]

I'm using ODBC drivers for Access (we publish to the Web via Enterprise).

They are Native Access Tables, not linked, and not Queries.

Access 2002 SP-2, ver 10.4302.4219

 

[FVTrainer]

Well, the problem appears to be with the ODBC driver not liking the syntax. I've seen that error not only in Crystal but in Excel, too, if your query isn't entered properly. The problem is trying to figure why that exact syntax works on my system and not on yours. I'm using version 2.7 of the MDAC, and I've installed the latest Hot Fixes from Crystal for CR 9, so somewhere in the interplay between those two components lies our answer.

I tried the following approach and still had to modify some of the code after I got it to Crystal: I went into Access and wrote a query against one of tables, selecting just the fields I wanted. I figured if Access could pass a SQL statement that the ODBC driver would like, then perhaps I could just cut and paste it into the Command object. So after writing a basic query (no UNION or WHERE clause) I copied and pasted Access' SQL code into the Crystal command object. It worked fine until I added a WHERE clause from the Access Query Designer. Access put "" around the value (as in H.status = "P&quot, as well as a bunch of parens. When I pasted the WHERE clause into the Command object I got the same error message as you did, so I stripped out the parens and replaced the "" with ''. Then the query worked.

My working query (with my table names) looks like this when using Access' Query Designer as the basis for building the query:

SELECT [trade-blotter].SNAME, [trade-blotter].TradeDate, [trade-blotter].FundID, [trade-blotter].Amount
FROM [trade-blotter]
WHERE [trade-blotter].TranType='B'
UNION ALL
SELECT trade_history.SNAME, trade_history.TradeDate, trade_history.FundID, trade_history.Amount
FROM trade_history
WHERE trade_history.TranType='B' Or trade_history.TranType='C';

You can check your version of the MDAC by downloading the MDAC component checker from the following location at microsoft:

http://www.microsoft.com/downloads/...familyid=8f0a8df6-4a21-4b43-bf53-14332ef092c9

MDAC 2.7 (or 2.8 for that matter) can be found at:

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001860

Another thing you might try is entering just the first SELECT and FROM statement (without the WHERE or UNION statements) and seeing if the syntax for just the first part is right. Perhpas we can narrow down exactly where the syntax problem is on your system.

 

[lbass]

You didn't really explain what kind of results you were trying to achieve, but instead of a union query (if you can't get it to work), you might want to try linking the current and history table by both SS# AND date. You'd probably want a left join from the current field to the history field if you want to include cases where there is only a current status.

This would return only nulls or records with matching dates from the history table, I think. Not sure that's what you need though.

-LB

 

[amoeba102]

This is what my command looks like after determining that I was, actually using the wrong tablename. It should be Status History rather than Status_History.

select B.Status, B.Status_date
from [BGS-Data] B
where B.Status = 'P'

Union All

select H.status, H.date
from [Status History] H
where H.status = 'E' or H.status = 'P'

I also check my version of MDAC (thanks for the links) and found it to be 2.5 SP3, so I downloaded and installed 2.7 REF (Looking at the descriptions I didn't feel comfortable with 2.8)

When I stripped off everything after the first select/from/where I still get the 'too few parameters, expected 1' message.

 

[FVTrainer]

You know, I think there is an easier way to build your (by now I'm think of this as "ours&quot command object. Start a new crsytal report using the same odbc connection, but select only the first table (BGS-Data) as your data source. Drop the fields you need in the report and create your record selection formula. Preview the report and verify that you are getting the records you want. Then, go to the Database | Show SQL option. Copy this sql code and paste it into a new command object. Repeat these steps for second table you want (i.e., create a new report, etc) and then paste it into the command object your created above, adding the UNION ALL statement between the two statements. Also, make sure that the fields in the Select statements are the same and in the same order. If that doesn't work, I think I'm out of suggestions.

 

[amoeba102]

Whooooooooppppeeeeeeeeeeeeeeeee!


Thank You !!!

FYI, here's what it ended up:


SELECT `BGS_Data`.`SS#`, `BGS_Data`.`Status`, `BGS_Data`.`Status Date`
FROM `BGS-Data` `BGS_Data`
WHERE `BGS_Data`.`Status`='P'

UNION ALL

SELECT `Status_History`.`SS#`, `Status_History`.`Status`, `Status_History`.`Date`
FROM `Status History` `Status_History`
WHERE (`Status_History`.`Status`='E' OR `Status_History`.`Status`='P')

 

[xsw1971]

Is "Add Command" only available in CR9? This is exactly what I need but I can't figure out how do do it with CR8.5

Thanks,
Jennie