Waylen,
I've re-read your original post and I'm still not sure exactly what
you're trying to do. My best guess is that you currently have tables
somewhat like this:
tblClients
ClientID, ClientName, ...
1, Acme Steelworks
2, Wooden Hose Ltd
tblInvoices
ClientID, Date, Amount
1, 5/5/04, 1234.56
1, 6/5/04, 2345.00
2, 5/5/04, 1000.00
2, 6/5/04, 2000.00
1, 6/5/04, 8765.43
and you want output like this
Acme Steelworks, 1234.56, 2345.00, 8765.43
Wooden Hose Ltd, 1000.00, 2000.00
One way to achieve this is to use the fConcatChild() function at
http://www.mvps.org/access/modules/mdl0004.htm to concatenate all the
amounts from tblInvoices - i.e. the child table - into a single string.
There's an example there that works with the Northwind sample database.
(You'll probably need to modify the fConcatChild() code to replace the
semicolon it uses to separate the concatenated items with whatever you
need to separae the fields in your output file, e.g. a tab character.
There's a trick going on here: Access thinks that all the concatenated
items form a single field, while the software or person that reads the
output file will see them as multiple fields.)
Another approach is to start with a query that joins the two tables and
adds a calculated "record number" to give you something like this:
Acme Steelworks, 1, 1234.56
Acme Steelworks, 2, 2345.00
Acme Steelworks, 3, 8765.43
Wooden Hose Ltd, 1, 1000.00
Wooden Hose Ltd, 2, 2000.00
Then use a crosstab query to re-arrange the data.
John,
I can't seem to find information on the fconcat() at
www.mvps.org/Access
Can you please give me an example of how to use it please?
~Waylen...
-----Original Message-----
Hi Waylen,
Depending on just what you want ot achieve, you could use either a
report (exported as plain text) with grouping and headers, or the
fConcat() or fConcatChild() functions in the Access Web site
http://www.mvps.org/Access
I have two access tables that are used for invoicing. One
table contains the client information. The second
contains the invoicing information. They both share the
Invoice# for the join.
Here's the problem.
I need to export this data to a flat table that includes
the Client Information from the first table, and 1 field
of data from the second table for every invoice record. I
can have as many as 20 invoices per 1 client.
How can I combine the information so that I can get a
single record for each client that contains the client
information with the up to 20 fields of data from the
second table in the same record?
Please help!
~Waylen...
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
