Group by??

[sstump]

First I have to say I do not know SQL or VB very well...so if I'm able to get this to work without learning a new language then I'll be ecstatic.

OK...I have a tbl of approximately 1000 lines of data and about 20-30 columns each. The actual query itself is irrelevant, but I'm meshing this up against multiple other tbls that I'm linked too. What I need is this 1000 lines of data to remain in tact (at least what's left of them) when the query is complete. However when it is complete the 1000 lines of data turns into 15000 lines of data. If anything I should only see a fraction of the original 1000 records (maybe 300-400). I can't do group by on something so large and with so much information.

There is one field that is distinct in every line of my original tbl. How can I do a group by on that one field only though. I've seen the SELECT DISTINCT command used before and tried to build that into my SQL statement but it doesn't work. Should I "Make new table" with the 15000 lines and then build a new query with SELECT DISTINCT command, if so what is the correct format if this is all I want it to do.

Sorry if this is tough to understand.

 

[R00K]

Can you post an example of your query and maybe the structure of the tbl?

 

[sstump]

If it helps sure...here is the SQL query for it. It's alot of information and alot of tables involved, but here we go.

SELECT tbl1.ID, tbl1.VENDOR_SUMMARY_NAME, tbl1.BILL_DATE, tbl1.BAN, tbl1.STATE, tbl1.ACNA, tbl1.so_number, tbl1.[so_completion date], tbl1.PON, tbl1.[ANI (WTN)], tbl1.[ANI(TN)], tbl1.audit_number, tbl1.occ_from_date, tbl1.occ_thru_date, tbl1.mrc_nrc_indicator, tbl1.phrase_code, tbl1.phrase_desc, tbl1.usoc, tbl1.usoc_description, tbl1.amount INTO tblIlecAll
FROM ((((ARADMIN_BASE_VENDOR_REFERRAL_NOC RIGHT JOIN (tbl1 LEFT JOIN ARADMIN_BASE_TROUBLE_TICKET_NOC ON tbl1.[ANI(TN)] = ARADMIN_BASE_TROUBLE_TICKET_NOC.ORIGINATING_ANI) ON ARADMIN_BASE_VENDOR_REFERRAL_NOC.ATTACHED_TO_ID = ARADMIN_BASE_TROUBLE_TICKET_NOC.TROUBLE_TICKET_ID) LEFT JOIN CLEC_CLEC_ANIS ON tbl1.[ANI(TN)] = CLEC_CLEC_ANIS.ANI) LEFT JOIN CLEC_ORDER_STATUS ON CLEC_CLEC_ANIS.CUST_ID = CLEC_ORDER_STATUS.CUST_ID) LEFT JOIN CLEC_ACTIVITY_TYPE_CODE ON CLEC_ORDER_STATUS.ACTIVITY_TYPE_CODE_ID = CLEC_ACTIVITY_TYPE_CODE.ACTIVITY_TYPE_CODE_ID) LEFT JOIN CLEC_NFMSG_AF_PROV_COMPLETE ON CLEC_ORDER_STATUS.ORDER_ID = CLEC_NFMSG_AF_PROV_COMPLETE.ORDER_ID
WHERE (((ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_DATE_TIME) Between [tbl1]![10 days before] And [tbl1]![10 days after] Or (ARADMIN_BASE_TROUBLE_TICKET_NOC.CLOSED_DATE_TIME) Is Null) AND ((CLEC_NFMSG_AF_PROV_COMPLETE.SOC_DATE) Not Between [tbl1]![5 days before] And [tbl1]![5 days after] Or (CLEC_NFMSG_AF_PROV_COMPLETE.SOC_DATE) Is Null));

The first two lines of the original table are as follows, the "so_number" field is the distinct field that is different for every line(just cut and paste into Excel to view):

ID VENDOR_SUMMARY_NAME BILL_DATE BAN STATE ACNA so_number so_completion date PON ANI (WTN) ANI(TN) audit_number occ_from_date occ_thru_date mrc_nrc_indicator phrase_code phrase_desc usoc usoc_description amount 5 days before 5 days after 10 days before 10 days after
64 SOUTHWESTERN BELL CLEC 10/5/2003 1105570028539 MO Z4G A006418 9/15/2003 UNE030913008 573-883-7062 UNE030913008 9/12/2003 9/12/2003 2 648 ADJUSTMENT FOR MAINTENANCE OF SERVICE CHARGE TECHS=1;SERVICE RESTORED ON 09/12/03;RPT NULL NULL $37.00 9/10/2003 9/20/2003 9/5/2003 9/25/2003

 

[sstump]

Anyone have any suggestions on how to do a group by on this by so_number?

 

[MakeItSo]

Hmmh - you can only group over all fields in a query.
The way I see it, your table contains more data than you actually need. Ever thought about just keeping the vital data in the table and then calculating other data only at the time you need it (e.g. on a report/in a query)?

You could also group all fields and select "Max, Min, Sum, Average" or whatever from the grouping dropdown box in query designer.

Would that solve your problem?

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[sstump]

I wish I could just remove the data...however I need the final report to show the initial table and then the additional information. So I can't remove them.

 

[MakeItSo]

I assume the data has been collected chronologically so that the last enterd value is the correct one?

There's an option "Last Value" when grouping:
In the query designer, click on the Sum symbol.
Then choose Group for your one special field and "Last Value" for all others.

Hope this does it.

P.S: Remember to always backup your db before making experimental changes to the data!

Regards,
Andy