Query hell

[VBmim]

Hello

I'm almost pulling my hair out here, so I hope someone can help me.

I'm trying to make a report in visual basic with the rdc component (crystal 8), I'm pulling my data from an access 2000 database.

I have two tables: SALES and DELIVERY-CODE. In the table DELIVERY-CODE I have somewhat 15 codes
code descr
0 "TRANSPORTFIRM A"
1 "TRANSPORTFIRM B"
2 "POST"
3 "TRAIN"
....

Every entry in my sales db has among others fields with delivery code, a payement code, date and total amount.

The report I am trying to pull is (for a certain date)

PAYEMENT CODE 1 2 3 4
DELIVERY CODE
------------------------------------------
TRANSPORTFIRM A 50 0 0 20
TRANSPORTFIRM B 0 0 0 0
POST 0 30 0 0
...


The problem is when for a certain date, no purchase has been made with delivery code 1 (TRANSPORTFIRM B). The line doesn't show up in my report.

I already tried:

* specifying join type in visual linking expert: the option was disabled. I looked in to that and people pointed at the option of using ODBC instead of Database files (which I used). When I did this, I could specify linking options, but I got the very descriptive "server has not yet been opened" when I tried to show my report and I never got rid of it.

* making a sql query in access... I never got the results I had to have
Query: select DELIVERY-CODE,PAYEMENT-CODE,sum(AMOUNT) from DELIVERY-CODE left join SALES on DELIVERY-CODE.DELIVERY-CODE = SALES.DELIVERY-CODE where SALES.PURCHASEDATE = #05-10-2005# GROUP BY DELIVERY-CODE,PAYEMENT-CODE.
Again, the results are only the delivery codes that are present in the sales table for that day

* tried using subreports. The main reports had all the records in the DELIVERY-CODE table, and the subreport got all the SALES-DATA linked to the DELIVERY-CODE.
There, I got a row per DELIVERY-CODE, but the codes where no sales were found stayed blank (instead of showing 0 and a description).

So... what options do I have left, or what am I doing wrong...

Sorry for lengthy post...

greetz

VBMim

 

[synapsevampire]

You specifically state in your SQL that you only want rows that are for a days sales, why are you expecting otherwise?

Try creating a query to select all of the delivery codes, then left outer join it to a in line query (derived table) of the joins for the query you already have.

Or with this "There, I got a row per DELIVERY-CODE, but the codes where no sales were found stayed blank (instead of showing 0 and a description).", turn on the File->Report Options->Convert null value to default.

-k

 

[VBmim]

Hello synapsevampire

Thanks for your response.

I was expecting all rows from DELIVERY-CODE + matching records in SALES because I was using left join, but obviously that is not true.

Try creating a query to select all of the delivery codes, then left outer join it to a in line query (derived table) of the joins for the query you already have.

I managed to make an access query that gives me the result I want but I can't access it through crystal reports. I guess because I set a parameter in the query (the date).

Or with this "There, I got a row per DELIVERY-CODE, but the codes where no sales were found stayed blank (instead of showing 0 and a description).", turn on the File->Report Options->Convert null value to default.

I re-tried the sub-report thing and got it to the point of getting all the delivery codes and descriptions, but where there is no matching SALES records, the report shows blanks instead of 0. Logical, because there is no record to be shown so default value cannot be used...

Anyhow, this report was to replace a vbprogram very badly written (with variables x and y and so forth). I was asked to change the report a bit and I decided that a crystal report would do just fine. Because the changes had to be made quickly I ended up changing the yukkie-code. (yukkie!)

Thanks for your help anyway!

Greetz