How to generate a report with distinct values

[Shailja]

Hello,

I have developed Drug Management Software in VB. Now I want to generate one report showing the list of all medicine name, Qty, Expiry Date etc. using Crystal Report.

Format would be like this:

Item Name Batch No. Qty Expiry Date
Crocin B101 100 10/2009
B102 150 9/2007
--------
250

Metacine B201 250 5/2008
B202 170 6/2010
--------
420
I am fetching above details from Purchase Master table. Table can have more than one enteries of same medicine bcoz it can be purchased from more than one supplier at different rate. But I have to restrict the name of Medicine and Batch by displaying only once.

I have written following query but it does not work:
SELECT DISTINCT(ItemName), DISTINCT(BatchNo) FROM PurchaseMst GROUP BY ItemName

Kindly give me solution if u know. I am on final stage of the project but I am stucked.

[shidec]

try this:

SELECT DISTINCT ItemName, BatchNo FROM PurchaseMst

[brazel]

Good day!

Hello,


Try also this Idea if can give you a help.

If you are using vb crystal report, then try to used formating string set to suppress if duplicated. this command is found if you right click your mouse in the datafield in cystal report. if this is not helpful try this.

Make a table that can store your contents in a master file, I mean is to used two tables 1 table is your master, then the other is the table that can hold only once.

sample logic:

if you save a record to your masterfile then make a extension module the can
save to the second table. and Now let say that the 2nd table have the same files
as what in the masterfile record. then make a decision that can detect if the medicine name or description is already exist, if already exist then thats the time you only used the command "edit" then "update" so no description can be duplicated because you only edited. God Bless and Gud Luck!!!