You list three field names but show five fields of data.
What is different in the two records that have (cust2) as 30?
Could it be that the data in the Prime is different like having a leadon
space?
Try doing some data analysis like this --
SELECT Prime, Count([Prime]) AS Prime_Count
FROM Table1
GROUP BY Prime;
See how many records you get. Does it match your number of main billing
codes?
Do the same for the other fields - count of Cust2 compared to number of
departments.
--
Build a little, test a little.
HubbyMax said:
(prime) (DTL) (cust2)
523 80 108140 4031 30 (only shows 1, should show 2 sorted by prime)
523 80 145130 4031 50
This is what happens if I group by cust2. It is sorted properly but does
not show each order for "30"
If I group by prime both of "30" are listed but not together.
:
Post example of output data that did not sort for you.
--
Build a little, test a little.
:
When put the Cust2 as the grouping I only get 1 order listing for each
customer showing the total for all orders by the customer. If I change it to
group by Prime and sort by Cust2 I get all orders by all customer orders and
billing codes but they are not sorted by Cust2. I need it to show Cust2 and
list all billing codes used by that customer with the total cost to each
billing code. It should then go to the next Cust2 and do the same. The totals
are not an issue as they are handled by the query.
:
There should be no problem in sorting like you want.
Post example of output data that did not sort for you.
--
Build a little, test a little.
:
I have tried to sort on 2 or three fields using the group/sort function but
only get results for the 1st "group" with no sorting.
I would want
Cust2 as the group
Prime as the main sort of a group
DTL as the refining sort of the group
:
You do know that you can sort on more than one field.
I need you to draw me a picture of what you want like this --
Cust2
Prime
DTL
Prime
DTL
Cust2
You can use your query to prduce two reports. Then place them inside of a
third as subreports. The third, being your main report, does not need any
source but just be a vehicle for the other two.
--
Build a little, test a little.
:
)OK. I guess I am creating a sub report then. It is a seperate report that
draws it's info from the same query as the main monthly report. I have tried
setting the grouping and sorting but can only get it to sort on whatever
field I choose as the group. If I set cust2 as the "group" it groups the
records by group but only shows 1 total recoed for each group. If I set the
group as Prime or DTL I get all db entries of each cust2 but it is not sorted
by cust2. How do I get it to sort the records by cust2?
:
The subreport is a separate report and therefore has separate grouping and
sorting. It can be linked to the main so as to report subsets of main
categories or produce a totally independant data.
--
Build a little, test a little.
:
This does not help much. Wouldn't I still have the same group and sorting
question?
:
I suggest trying a sub report.
--
Build a little, test a little.
:
I have an inventory report that I need help with. I have a monthly report
that shows all items despersed each month that shows each requisition and
items ordered on that requisition. This report is sorted and grouped by
department. I now need to creat a summery report that only shows the
departments billing code, the department name, and the total amount charged
for each requisition. This must be grouped by department the sorted by
billing code.
The three fields of the db used are Cust2 (department code),Prime (main
billing code), and DTL (secondary billing code). I have been able to creat a
report that either groups by Cust2 or by DTL and Prime but can not get it ot
group by Cust2 then sort that group by Prime then DTL.
