Get latest dated record -multple tables -multiple field names 1

[nja5150]

Mpastore helped me with this issue...it was simpler at the time

I need to return the most up to date record entered by the invdate AND the receipt in the respective tables below. I need to return the address information for that most current record. The invoicenum will be the same in both tables after some work. Invoicenum has multiple records in both tables.

Invoices Table
---------------
Invoicenum = Invoice number
invdate = Invoice date
Name = Name
Addr_1 = Address line 1
City = city, st & zip

Transfers Table
----------------
Invoicenum = Invoice number (with prefix added like -001)
receipt = Invoice date data in this table
B_Name = Name data in this table
B_Addr = Address data in this table
B_City = city, st & zip data in this table

Mpastore solved my previous problem (thanks) with the solution below. At the time I was only aware of the invoices table. The solution was as follows:

>something like...
>
>select * from invoices i1 where invdate in(
>select max(invdate) from invoices
>where invoicenum = i1.invoicenum)
>
>Assumes your invoice table named invoices, field invdate >has date, field invoicenum has invoice number.
>
>Create a new query and paste it into the SQL

Since then I have found ANOTHER table with information on the invoices related to the invoices tables above. The invoice numbers DO NOT match up right now. They are the same up until the 8th digit but then "- 001" gets added as a suffix. I think I can use VB or make table query to make a copy of the table. I can then code to strip out the "- 001" by programing a field length change on that property. This would get the invoice fields to match up at least (let me know if I'm thinking along the right lines.

The real problem however is that in this secondary table the field names and number of fields are different from the main table. In the transfers table the date data is in the field named receipt. The name and address information is also in fields with different names than are is in the invoices table. The goal of this whole thing is to pull the most up to date address out of the data. Whoever programed the db in the first place wasn't consistent with the names or where the data is stored.

I thought I could use VB to change the field names, field lengths and add columns to the Transfers Table so that both tables are consistent. After that I would code to append the records of the secondary table to the first and run the SQL query that is coded above by mpastore. This probably isn't very efficent or elegant but it's the only way I could come up with to get it done(my knoledge and understanding is limited). HOWEVER I can't find any reference to VB code that will change field names (column names at the table level)---I don't even know if you can do that. I have no experience with SQL, but I think I might be able to work around the field names and number of fields issue using SQL commands leaving the tables untouched.

I have to program this process for repetition because I am working with an ongoing legacy dbase app and I'll need to run this again as more data piles up.

If anyone can provide help / ideas I would greatly appreciate it!!!!

 

[mpastore]

I answered also in the 5/23 thread, let's kill that one as the issue belongs here in the SQL forum:

you can create column aliases in SQL, for example:

select receipt as invdate from mytable.

Create a make table query, select all fields and drop them into the grid. Before each field that you want to change the name of put the field name followed by a colon, e.g.

invdate: [transfers].[receipt]

When the new table is created it will have the receipt data in a field called invdate (or whatever you name it in query grid).



Mike Pastore

Hats off to (Roy) Harper

 

[nja5150]

Realized after I posted it was in the wrong forum I'll kill it now...

as usual mike thanks for your help!