If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A GROUP BY QUESTION ....
hello everyone:
I have a table structure like the one bellow: HEADER1 Enterprise Header2 Client #1 Detail Details of client #1 Detail Detail Trailer2 Footer of client #1 Header2 Client #2 Detail Details of client #2 Detail Trailer2 Footer of client #2 Trailer1 Footer of Enterprise The records must appear in this order i have five forms where i populate the fields of those 5 tables to make a relation between tables Header2, Detail and Trailer2 i create the field Client I really wanna know how should i do in order to have my records sorted in the way i show above . This is supposed to become a TXT file to send by e-mail i have tried the UNION ALL issue but i think a GROUP by statement.... but i am not so shure about it.... I would appreciate very much all the help that everyone can give to me.... Thanks in advance Best regards to all of you |
#2
|
|||
|
|||
Hi Jose,
Please tell us more about the tables, the fields they contain, and the relationships between. On Thu, 7 Apr 2005 06:00:03 -0700, "Jose" wrote: hello everyone: I have a table structure like the one bellow: HEADER1 Enterprise Header2 Client #1 Detail Details of client #1 Detail Detail Trailer2 Footer of client #1 Header2 Client #2 Detail Details of client #2 Detail Trailer2 Footer of client #2 Trailer1 Footer of Enterprise The records must appear in this order i have five forms where i populate the fields of those 5 tables to make a relation between tables Header2, Detail and Trailer2 i create the field Client I really wanna know how should i do in order to have my records sorted in the way i show above . This is supposed to become a TXT file to send by e-mail i have tried the UNION ALL issue but i think a GROUP by statement.... but i am not so shure about it.... I would appreciate very much all the help that everyone can give to me.... Thanks in advance Best regards to all of you -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
Hi John Nurick
Can i reply to your Mail? The details will be more accurate. just let me know how do you want it. Thanks for your help Best regards -----Original Message----- Hi Jose, Please tell us more about the tables, the fields they contain, and the relationships between. On Thu, 7 Apr 2005 06:00:03 -0700, "Jose" wrote: hello everyone: I have a table structure like the one bellow: HEADER1 Enterprise Header2 Client #1 Detail Details of client #1 Detail Detail Trailer2 Footer of client #1 Header2 Client #2 Detail Details of client #2 Detail Trailer2 Footer of client #2 Trailer1 Footer of Enterprise The records must appear in this order i have five forms where i populate the fields of those 5 tables to make a relation between tables Header2, Detail and Trailer2 i create the field Client I really wanna know how should i do in order to have my records sorted in the way i show above . This is supposed to become a TXT file to send by e-mail i have tried the UNION ALL issue but i think a GROUP by statement.... but i am not so shure about it.... I would appreciate very much all the help that everyone can give to me.... Thanks in advance Best regards to all of you -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. . |
#4
|
|||
|
|||
Hello John Nurick
i have a better thought i will reply here and if you have any question , then , according to you i will reply by mail if you allow me... THEADER1 ID TEXT 2 (H1) Enterprise TEXT 9 NIF TEXT 13 Date TEXT 8 THEADER2 ID TEXT 2 (H2) Cliente Text 11 InvoiceNr Text 9 InvoiceDate Text 8 YYYYMMDD TDETAIL Tipe Text 2 (D2) Client Text 11 Date Text 8 YYYYMMDD Article Text 13 Qty Text 9 Value Text 11 TFooter2 Tipe Text 2 (T2) Client Text 11 Records Text 11 (Count records from client including THeader2 and TFooter2) TFooter1 Tipe Text 2 (T1) Records Text 11 (Count records from enterprise including Theader1 and TFooter1) The relationships between the tables should be like that 1 to MANY - THeader2 - TDetail MANY to 1 - TDetail - TFooter2 Field to make the relation - Client I think i have answered all the questions you mentioned on your reply.... Thanks in advance Jose -----Original Message----- Hi Jose, Please tell us more about the tables, the fields they contain, and the relationships between. On Thu, 7 Apr 2005 06:00:03 -0700, "Jose" wrote: hello everyone: I have a table structure like the one bellow: HEADER1 Enterprise Header2 Client #1 Detail Details of client #1 Detail Detail Trailer2 Footer of client #1 Header2 Client #2 Detail Details of client #2 Detail Trailer2 Footer of client #2 Trailer1 Footer of Enterprise The records must appear in this order i have five forms where i populate the fields of those 5 tables to make a relation between tables Header2, Detail and Trailer2 i create the field Client I really wanna know how should i do in order to have my records sorted in the way i show above . This is supposed to become a TXT file to send by e-mail i have tried the UNION ALL issue but i think a GROUP by statement.... but i am not so shure about it.... I would appreciate very much all the help that everyone can give to me.... Thanks in advance Best regards to all of you -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. . |
#5
|
|||
|
|||
Hi Jose,
1) Queries are not designed to do what you are trying to do. In a relational database like Access, a query returns a recordset in which all the records have the same field structure. For output such as you describe in your first message, one would normally use a report (for the header and enterprise lines) containing a subreport (for the detail lines). Reports also make it easy to provide totals and subtotals, and can be exported to text files. 2) Your table structure makes things unnecessarily difficult. Tables should never be designed to match your desired output: instead, they must be appropriate to the real-world entities which your database is modelling (e.g. enterprises, clients, orders, order details). The Northwind sample database installed with almost all copies of Access shows a typical structure. In particular: - Tables TFooter1 and TFooter2 seem unnecessary. Everything in them seems to be derived from data in THEADER2 and TDETAIL. - THEADER1 does not seem to be related to THEADER2. Consequently there is no way of ensuring that the correct records are drawn from the two tables. On Fri, 8 Apr 2005 07:08:47 -0700, "Jose 2" wrote: Hello John Nurick i have a better thought i will reply here and if you have any question , then , according to you i will reply by mail if you allow me... THEADER1 ID TEXT 2 (H1) Enterprise TEXT 9 NIF TEXT 13 Date TEXT 8 THEADER2 ID TEXT 2 (H2) Cliente Text 11 InvoiceNr Text 9 InvoiceDate Text 8 YYYYMMDD TDETAIL Tipe Text 2 (D2) Client Text 11 Date Text 8 YYYYMMDD Article Text 13 Qty Text 9 Value Text 11 TFooter2 Tipe Text 2 (T2) Client Text 11 Records Text 11 (Count records from client including THeader2 and TFooter2) TFooter1 Tipe Text 2 (T1) Records Text 11 (Count records from enterprise including Theader1 and TFooter1) The relationships between the tables should be like that 1 to MANY - THeader2 - TDetail MANY to 1 - TDetail - TFooter2 Field to make the relation - Client I think i have answered all the questions you mentioned on your reply.... Thanks in advance Jose -----Original Message----- Hi Jose, Please tell us more about the tables, the fields they contain, and the relationships between. On Thu, 7 Apr 2005 06:00:03 -0700, "Jose" wrote: hello everyone: I have a table structure like the one bellow: HEADER1 Enterprise Header2 Client #1 Detail Details of client #1 Detail Detail Trailer2 Footer of client #1 Header2 Client #2 Detail Details of client #2 Detail Trailer2 Footer of client #2 Trailer1 Footer of Enterprise The records must appear in this order i have five forms where i populate the fields of those 5 tables to make a relation between tables Header2, Detail and Trailer2 i create the field Client I really wanna know how should i do in order to have my records sorted in the way i show above . This is supposed to become a TXT file to send by e-mail i have tried the UNION ALL issue but i think a GROUP by statement.... but i am not so shure about it.... I would appreciate very much all the help that everyone can give to me.... Thanks in advance Best regards to all of you -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. . -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Total hours from Table into Report | HeatherLou1974 | General Discussion | 3 | March 7th, 2005 08:04 PM |
O.E. Address Book Groups | Joy | Outlook Express | 12 | February 2nd, 2005 04:13 AM |
Group By Date question - date format needs to be "general" | chris | Running & Setting Up Queries | 1 | October 25th, 2004 09:09 PM |
Group Naming | Group Me | Outlook Express | 1 | August 9th, 2004 05:52 AM |
Group By | Running & Setting Up Queries | 3 | August 5th, 2004 10:37 PM |