Modify data from a ODBC Link

  • Thread starter Thread starter Brian
  • Start date Start date
B

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?
 
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
 
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.
 
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
 
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.
 
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.
 
Back
Top