Show 1st Item from Child Table When Showing Records in Parent Table 
Author Message
 Show 1st Item from Child Table When Showing Records in Parent Table

I have Access 2000, and a "parent" table named
<tblInvoices> with a "child" table named
<tblInvoicesLineItems>. Most entries in the "parent" table
have only 1 related entry in the "child table, though a
scattered few (about 1%) have 2 or 3 (though no more)
related entries in the "child" table.

I want the query to join the two, show every record in
the "parent" table and show only the 1st related entry
that links to it in the "child" table. I only need to show
the contents of the field "account_number" from
the "child" table.

Any tips?

LRH



Sat, 28 May 2005 06:26:26 GMT  
 Show 1st Item from Child Table When Showing Records in Parent Table
Hi Larry,
Include all the fields from the parent table, the
accountnumber field from the child table, and for the
totals column for the account number, set it to First.
That should give you what you are looking for.
HTH
Ed Voss
MCP
Quote:
>-----Original Message-----
>I have Access 2000, and a "parent" table named
><tblInvoices> with a "child" table named
><tblInvoicesLineItems>. Most entries in the "parent"
table
>have only 1 related entry in the "child table, though a
>scattered few (about 1%) have 2 or 3 (though no more)
>related entries in the "child" table.

>I want the query to join the two, show every record in
>the "parent" table and show only the 1st related entry
>that links to it in the "child" table. I only need to
show
>the contents of the field "account_number" from
>the "child" table.

>Any tips?

>LRH
>.



Sat, 28 May 2005 06:45:29 GMT  
 Show 1st Item from Child Table When Showing Records in Parent Table
Ed,

It is highly unlikly that this technique will work reliably.

Larry,

What field(s) do the two tables have in common, to link by
(InvoiceID)?

What field in your InvoiceLineItems table will identify the "first"
entry for a particular InvoiceID?

I would have thought that the [Account_Number] field would have been
in your Invoices table, rather than in the InvoiceLineItems table.

--
HTH

Dale Fye

****** Avoid Spam **************
To reply directly, modify my email address

************************************


Hi Larry,
Include all the fields from the parent table, the
accountnumber field from the child table, and for the
totals column for the account number, set it to First.
That should give you what you are looking for.
HTH
Ed Voss
MCP

Quote:
>-----Original Message-----
>I have Access 2000, and a "parent" table named
><tblInvoices> with a "child" table named
><tblInvoicesLineItems>. Most entries in the "parent"
table
>have only 1 related entry in the "child table, though a
>scattered few (about 1%) have 2 or 3 (though no more)
>related entries in the "child" table.

>I want the query to join the two, show every record in
>the "parent" table and show only the 1st related entry
>that links to it in the "child" table. I only need to
show
>the contents of the field "account_number" from
>the "child" table.

>Any tips?

>LRH
>.



Sat, 28 May 2005 21:14:07 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Unless I create child record, parent record won't show

2. Subform: Parent/Child tables but no Child records.........

3. Zip Code Table Used as Parent Table for Two Child tables on a form

4. Info from child table and parent tables on the same form

5. Info from child table and parent tables on the same form

6. Info from child table and parent tables on the same form

7. Efficiency of Queries Returning Distinct on a Parent Table filtered on a Child Table

8. Taking data found in Child Table in placing into a memo field in Parent Table

9. Access only shows 1st 197 chars of field in linked table

10. Access only shows 1st 197 chars of field in linked table


 
Powered by phpBB® Forum Software © phpBB Group