Modify data from a ODBC Link

[Brian]

I have created a link using ODBC to a read-only table and I want to produce a
table from it.
I have a few requirements that need to be met.
1. Reduce the ODBC Table to only records Equal/Greater to 01/01/07
2. Collect similar Check Numbers from a Check Field and add them to ONE new
record in the new table.

Example:
ODBC Table (Read Only)
CHECK NO. CHECK DATE AMOUNT INVOICE NO
100 01/02/07 5.00 A1
220 04/01/07 10.00 5000
100 01/02/07 300.00 A2
50 11/31/06 500.00 30


Access Table (Results)
CHECK NO. Check DATE INVOICES AMOUNTS
100 01/02/07 A1,A2 5.00,300.00
220 04/01/07 5000 10.00

Note that it did not write Check 50 because it was older than 01/01/07.

I want this to happen from a command button on a form and I realize that it
will ned to include a programming. Can this be done and how?

 

[Dale Fye]

Yes, it can be done. But it is never a good idea to store more than one
piece of information in a single field (as in your Invoices and Amounts
fields). What is the purpose of this?

Dale

 

[Brian]

The purpose is that I have one to many invoices that are being paid by one
check. I need to identify the invoice number (s) and the amount (s) that
total the amount of the check. This needs to be in one table.

 

[Dale Fye]

Brain,

Why not setup your invoice as a Report/subreport. You can create put the
invoice basics on the report, then use the subreport to provide the details,
which would include the individual invoice #'s as well as the amounts, and
would provide a total at the bottom of the invoice.

If you are interested in this, and need some help with the specifics, then
post back.

Dale

 

[Brian]

Dale,

Basically I need to have all this information in one table for a seperate
project. The ODBC table is read only from a totally different application
that is not Access compliant, or in any other standard database format. They
do provide an ODBC export as a method of getting to the data, so that is what
I am left with. The data that I extract then needs to be written to another
table for another application. It has to be wrtten to one table for it to
work correctly.

 

[Dale Fye]

Brian,

Check out this function on the Access Web (the Access MVP site).

http://www.mvps.org/access/modules/mdl0004.htm

It is a function that you can pass several values to and it will return the
values concatenated as you want them. BTW, this is a great site that has
lots of explainations and sample code, it is one of my top Access programming
bookmarks.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.