Report Grouping and Ordering

[dfuas]

Hi everyone,

I have a field in a table on Access 2000 database with takes the following information in any order and can be repeated. e.g. can have 10, etc people with contact signed, 10, etc people with PDD issued, etc.

contract signed
PIN issued
PDD issued
validation finished
Host country approval
EB approved
CER's issued.

I am designing a report in which I want to Group and order the info. in the field in the order shown above, e.g 1. contract signed, then 2. PIN issued followed by PDD issued, etc in Ascending order say and the other way round say CER's issued, EB approved, etc.

Can anyone help here? I have not much experience with designing reports but I have tried using the report Sorting and Grouping and it does it in aphabetical order, not in that order I require.

Thank you

dfuas

 

[Matt Greatorex]

Have you tried sorting the columns into the order you need, in the underlying query?

 

[dfuas]

Yes, but some how I don't get it to be grouped as above.
1. contract signed
2. PIN issued
3. PDD issued
4. validation finished
5. Host country approval
6. EB approved
7. CER's issued.

1-7 as Ascending
7-1 as Descending

 

[Matt Greatorex]

What does the SQL query actually look like?

 

[dfuas]

SELECT [Trade].[Status], *
FROM Trade
ORDER BY "contract signed", " PIN issued" DESC , "PDD issued" DESC , "validation finished" DESC , "Host country approval received", "EB approved" DESC , "CER's issued" DESC;

and I tried other combinations. But I may doing all wrong as well.

 

[Matt Greatorex]

Just to clarify, in the example you posted, the results look like they'd be sorted by:

contract signed (in ascending order) then
PIN Issued (in descending order) then
PDD issued (in descending order) then
validation finished in descending order) then
host country approval received (in ascending order) then
EB approved, CERs Issued (in descending order).

I take it this isn't right?

In simple English (forget the code for a minute), what should the sort sequence be?

 

[dfuas]

It should be in that order:

contract signed
PIN issued
PDD issued
validation finished
Host country approval
EB approved
CER's issued

contract signed to CER's issued as Ascending

and

CER's issued to contract signed as Descending

 

[Matt Greatorex]

Okay, if I've got this right (and apologies if not):

SELECT [Trade].[Status], *
FROM Trade
ORDER BY "contract signed", " PIN issued", "PDD issued", "validation finished", "Host country approval received", "EB approved", "CER's issued";

Should sort the columns in that sequence, with the actual data in ascending order.

SELECT [Trade].[Status], *
FROM Trade
ORDER BY "CER's issued", "EB approved","Host country approval received", "validation finished", "PDD issued", "PIN issued", "contract signed";

Should sort the columns in that order (the reverse of the first one), with the actual data also in ascending order.

 

[dfuas]

Status

contract signed
PDD issued
validation finished
CER's issued
contract signed
PIN issued
PDD issued
PIN issued
Host country approval received
PIN issued
Host country approval received
PDD issued
PDD issued
validation finished
Host country approval received
Host country approval received
EB approved
contract signed
contract signed
PIN issued
Host country approval received
CER's issued
PIN issued
contract signed
contract signed
PIN issued
PDD issued
PDD issued
PDD issued
validation finished
Host country approval received
EB approved
CER's issued
Host country approval received
PDD issued
PIN issued
contract signed
PDD issued
EB approved
validation finished

As you can see that what I got as a result by running the query. I need to group this together as of all PDD issued should be grouped together. I will try to do that using the Grouping and sorting in report to see if it will work. What do you think?
Thanks a lot
dfuas

 

[Matt Greatorex]

Sorry, I'm really not helping you much, here.

Re-reading your problem, I see that all of these values are in the same field (Status).

I incorrectly thought there were seven fields, called
contract signed
PIN issued
PDD issued
validation finished
Host country approval
EB approved
CER's issued?

Would it be an option to add another field and use that to store the sort sequence (e.g. 1 next to contract signed, 2 next to PIN issued, and so on). Sorting could then be done on that field, with no problems.

Does anyone with a bit more experience have some advice to offer?

 

[dfuas]

you are right. anyway I appreciate you input.
I though about that sort sequence before but I have not enough experience to carry it out.
I need more help here.
Thanks a lot
dfuas