Desperate Newbie

I REALLY need help with this and if someone would be kind enough to take the time, I would be forever grateful.

This is what I need my VB Report to look like:

Begin Group for Item_Number 123
First Record containing AUsed for Item Number 123
Next Record containing AUsed for Item Number 123
Next Record containing AUsed for Item Number 123
Total for AUsed (AUsedTotal)

Begin Group for Item Number 456
First Record containing AUsed for Item Number 456
Next Record containing AUsed for Item Number 456
Next Record containing AUsed for Item Number 456
Total for AUsed (AUsedTotal)

etc.

The best I could come up with in SQL is this:

SELECT Items.Item_Number, Items.Item_Name, Items.Category, Used.AUsed, Items.UOM, Items.Location,
SUM(Used.AUsed) AS AUsedTotal
FROM Items INNER JOIN Used ON Used.Item_Key = Items.Item_Key
GROUP BY Items.Item_Number, Items.Item_Name, Items.Category, Used.AUsed, Items.UOM, Items.Location;

The statement works in Access, but I'm not getting totals in the AUsedTotal field, just a repeat of the individual AUsed values in each row. But, the biggest problem is getting it to run at all in a VB Report DSR. I keep getting errors about the fields not matching and I'm ready to go insane (maybe it's too late !!)

I need some WONDERFUL person to tell me exactly how to set up and name the Group By headers and footers to get the report to resemble the above layout.

Is there a WONDERFUL person out there who has the time to "hold my hand" to get this done.

Like I said, I would be FOREVER grateful.

A desperate newbie thanks you.

I never used Data report, only crystal report, which I adise to all, but I will suggest next:

Begin Group for Item_Number 123
First Record containing AUsed for Item Number 123
Next Record containing AUsed for Item Number 123
Next Record containing AUsed for Item Number 123
Total for AUsed (AUsedTotal)

If you want display every record from the database, then dont use GROUP BY clause, it is used to group all records for 123 in one record, usually with some sum values, and here you want to display every record for 123 item, and in the end you want summary. I assume that there are some functions in data report that do summary, a SUM (or Total or how it is called) of AUsed field values and place it in the group footer section. And the SQL query would look something like this:

SELECT Items.Item_Number, Items.Item_Name, Items.Category, Used.AUsed, Items.UOM, Items.Location,
FROM Items INNER JOIN Used ON Used.Item_Key = Items.Item_Key ORDER BY items.item_number;


____________________________
If you find the answer helpful, please mark this topic as solved.

Thank you for the response.

I tried what you suggested and IT WORKED !

THANK YOU FOR A SIMPLE AND ELEGANT ANSWER.

i think i have same condition with u..

i need to display like this

table.month   table.year <---- command1_grouping
__________________________________________

table.category    table.data
table.category    table.data2
table.category2  table.data3   <------command1_details
table.category2  table.data4

i do not want table.category to be repeat then as u said , use order by.. however, it still repeated as it just ordered by alphabetical letter..help me goran.. i've no idea...

You dont give table(s) structure. Do you have to relate two tables, or you want to group records from one table only. Post table(s) structure and tell us exactly which fields you want to get in result, and what are fields grouped on.

____________________________
If you find the answer helpful, please mark this topic as solved.

my table structure

month year category data
________________________
jan     2000  cat1      abc
jan     2000  cat1      cde
jan     2001  cat2     asd

YOu shouldnt use Month and Year as field names, it can cause problems.....

SELECT ................ FROM TableName GROUP BY monthfield, yearfield,category

If you want data to be summarized for every category then you will add after SELECT statement SUM(data)

____________________________
If you find the answer helpful, please mark this topic as solved.