A GROUP BY QUESTION ....

  • Thread starter Thread starter Jose
  • Start date Start date
J

Jose

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
 
Hi Jose,

Please tell us more about the tables, the fields they contain, and the
relationships between.
 
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.


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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
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.


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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
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.




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.


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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top