Multiple SQL Queries on one Crystal Report

[anet]

I am working with an Access Database with a VB6 interface. I need to create a report that includes information that cannot be retrieved with a single SQL query.

I am a newbie to Crystal Reports and have only been able to create reports using one query. Is it possible to use more than one? If so, how would this be achieved.

Thanks.

 

[IdoMillet]

You could use subreports.

If you describe the situation in more detail,
perhaps a single SQL statement could be suggested.

Cheers,
- Ido ixm7@psu.edu

 

[anet]

Thanks for your response.

What I am attempting to do is create a report for balancing cash at the end of the day against the sales for the day.

When the cashier counts the cash it is entered on the computer with the number of pennies, nickels, dimes, etc. This is saved as one record in a table with the cashier ID and date as a concatenated primary key. I then need to retrieve this information for a summary report and total it under the heading Cash Counted ... so far so good.

However, in order to balance this against the sales for the day I need to retrieve information from the sales table which of course will have numerous records with the invoice number being the primary key.

Therefore, what happens is this: if the cashier's ID appears on 30 invoices that day the sql query returns 30 records and duplicates the "Cash Counted" 30 times.

I have not been able to come with any way to eliminate this in a single sql query.

Any help would be appreciated!

PS - I also have no idea how to do sub-reports!

 

[IdoMillet]

Yes, you can easily do this with a subreport (see online help since explaining it over e-mail is a bit tedious ))

You can also do it with SQL. Something like:

SELECT daily_balance.cashier_id, daily_balance.date, daily_balance.balance, sum(invoice.amount)
FROM daily_balance, invoice
WHERE daily_balance.cashier_id = invoice.cashier_id
AND invoice.date = my_date_parm
AND daily_balance.date = my_date_parm
GROUP BY daily_balance.cashier_id, daily_balance.date, daily_balance.balance

Cheers,
- Ido
ixm7@psu.edu

 

[anet]

Thanks again! I am using the sql statement and it is working when I run it in VB (I get all the correct amounts). I had to change it slightly because we are not storing a total in the invoice table, just the breakdown of each charge so I have something like:
sum(invoice.Parking), sum(invoice.Locker), etc. in my sql query.

However, how do I transfer the sum function to Crystal Reports. I tried writing a formula:
sum(invoice.parking) + sum(invoice.locker), etc. and inserting this into my report, but when I run the report all I get is a zero total. I also tried the same formula but without the sum function and still get zero. What am I doing wrong?

 

[IdoMillet]

anet,
You can base your report on the SQL query by using
Crystal's SQL Designer.

Cheers,
- Ido ixm7@psu.edu

 

[anet]

I still can't get this to work in Crystal Reports, but it works fine in VB. When I try call the report I get the following message;

Seagate Crystal Reports Database Error:
"You tried to execute a query that does not include the specified expression 'PrkFee' as part of an aggregate function."

Here is my exact SQL statement and when I check the values for each field in the VB window they are correct, however, as soon as I get to the line of code that calls Crystal Reports I get the above error.

sql = "select cash.code, cash.date, cash.totcash, sum(payment.PrkFee), sum(payment.CrdFee), sum(payment.LkrFee),
sum(payment.pst), sum(payment.gst) FROM cash, payment WHERE cash.code = payment.userID AND payment.trnsdte = #&quot; & CDate(txtDate) & &quot;# AND cash.date = #&quot; & CDate(txtDate) & &quot;# AND cash.code <> 'BK' GROUP BY cash.code, cash.date, cash.totcash&quot;

The problem is I don't know what to insert on my report to get this to work. I tried inserting all the database fields from the payment table and then writing a formula to total them, but it didn't work. The report has to be called from Visual Basic.

FYI - I can't do it as a sub-report because of the way the information needs to appear.

Thanks again for your help!!!

 

[IdoMillet]

Within Crystal, are you using a query created via the
Crystal Sql Designer?

Please post an exact copy of that query; The error message you received indicates that one of the columns in the SELECT portion is payment.PrkFee (without sum() around it). If you remove that column, the problem should go away.

Cheers,
- Ido ixm7@psu.edu

 

[anet]

I don't appear to have Crystal Query Designer installed on my system ( we are using Version 6). I went to the Seagate Crystal Reports Workstation Setup to see if it was an option to install, but there was nothing there.

I did look at the query by going to the menu and selecting Database, Show SQL Query and you are right. The sum is not appearing in front of any of the fields that I am trying to sum. However, the select portion of the query cannot be edited in that window, only the from and order by, so I am still in the same boat!

Any other ideas?

 

[IdoMillet]

In that case, I suggest you create a VIEW (QUERY in MS Access terms) in the database and use it as the data source.

Cheers,
- Ido ixm7@psu.edu