Filtering a query for the most current date 1

[nja5150]

Hello

I have a dbf file that has some record entries with the same invoice number. Each set of records with the same invoice has a sequence # field and date when it took effect. I need to write some code (or maybe a slick filter for my query) that will return just the 1 most current record (using the date field) from those records with the same invoice number.

I'm at a loss where to start. I'm not sure if this is possible from within a query or if I should code a procedure that will work its way through the table and create a new table or strip out the information I don't need. It looks like some invoices have up to 7 duplicates.
GENERALLY the last sequence # is typically the most up to date, but I have already found cases where this isn't the case...either due to blank fields or error in input. I'd rather drive this based on the date to be certain.

Thanks for any help and ideas

 

[mpastore]

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

 

[nja5150]

mpastore (or anyone else that can take a crack at this)

Okay my situation has hit a bump. I found ANOTHER table with information on the invoices related to the tables above. The invoice numbers DO NOT match up. They are the same up until the 8th digit but then "- 001" gets added as a suffix. I think I can make a copy of this table and strip out the "- 001" by programing a field length change. 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 secondary table the invdate is named receipt. 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. Let me recap my db situation so you can see what I am dealing with.

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

My thought was to use a query to make a duplicate table. Then use VB to change the field names, field lengths and add columns so that both tables are consistent. After that I would add the records of the secondary table to the first and run the SQL query that is coded above. This probably isn't very efficent but it's the only way I could come up with(my knoledge and understanding is limited). BUT I can't find any reference to VB code that will change field names (columns 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 using SQL ALIAS commands?

Whatever the solution it has to work so that the dates in the main table and secondary table are compared and the most currently dated record is returned -showing me the up to date name and date info. As you can see it gets further complecated because the date, name, and address fields are different in the tables!!!!

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

(mpastore, if you are out there you are getting a couple of stars as soon as I can get to a machine that isn't so bugged out---it won't let me do it here for some reason)

 

[mpastore]

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