What Expression to pick out specific rocords??

[Basshopper]

I am importing data to a table through a query such as transaction history by date. How would I just import the lastest date only?? My query is grabbing data from 3 live linked tables and importing into another table.Any suggestions

thanks

Mfg # Description Std Cost Last Cost Last Qty Date
ECJ-1VB1C103K DIODE $0.00 $0.00 49 3/15/04

ECJ-1VB1C103K DIODE $0.00 $0.00 11 3/16/04

 

[scriverb]

When you say the last date only are you saying you only want to import one record? Or one record for each Mfg. or what. Please explain exactly what you need.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[BSman]

How are you importing? If you are linking to a table in a database, then include on your selection query the criteria to select only records with the current (i.e. latest) date. For example, if you are importing every day and want to import another day's worth of data, set a variable equal to the maximum date in your existing data, then add one day to that value and select the records to import as the ones equal to that day and just do the same process for each new day.

 

[Basshopper]

My query is an append query that grabs data from 3 different tables and appends it to another table for doing work. I delete the existing data before appending new data. This is done everytime we quote a job. I am interested in quering the lastest date per mfg # only, so there are mulitple dates per mfg # and I just want to get the last one imported to my working table. I am looking for help in doing that criteria as you suggest equal to the maxiumn date per mfg #.Hope that clarifies.

thanks

 

[scriverb]

Here is some sample code to use for this purpose:

Select A.* FROM [i]yourtablename[/i] as A 
WHERE A/[datefield] IN (Select Top 1 A.[datefield] FROM [i]yourtablename[/i] as B Group By A.[Mfg #] Order By A.[datefield] Desc)
Order by A.[Mfg], A.[datefield];

This is pseudo code so copy and paste and change datanames and test. If any errors occur just post back and I will try to help solve them. I will be offline for a couple of hours.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

Hi Bob: Here is my attempt at the statement. Getting a From error.

(Select A.* FROM t_mpnmst max linked as A
WHERE A/[tnxdte_15] IN (Select Top 1 A.[tnxdte_15] FROM t_tnxhis max linked as B Group By A.[mpnnum_49] Order By A.[tnxdte_15] Desc)
Order by A.[mpnnum_49], A.[tnxdte_15]

thanks

Larry

 

[scriverb]

Had a typo in my original:

Select A.* FROM [t_mpnmst max linked] as A
WHERE A.[tnxdte_15] IN (Select Top 1 A.[tnxdte_15] FROM [t_tnxhis max linked] as B Group By B.[mpnnum_49] Order By B.[tnxdte_15] Desc)
Order by A.[mpnnum_49], A.[tnxdte_15];

Give the above code a go and let me know. I would suggest that you start naming your tables, queries, report, fields, controls, or any DB objects without the use of spaces. Either use the underscore(_) or use no spaces and capitalize the first letter of each key word in the name. For this reason you see that I bracketed all names above with sq. brackets. This tells ACCESS what is what. Other wise a space means the end of something and expecting the beginning of something else.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Basshopper]

HI Bob: This is the error message I am getting

You have written a subquery that can return more than one field without using the EXISTS resevered word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

I am assuming I put this statement in the criteria row??? Correct???

Larry

 

[PHV]

Perhaps something like this in the criteria part of tnxdte_15:
=(SELECT Max(tnxdte_15) FROM t_mpnmst AS B WHERE B.mpnnum_49=A.mpnnum_49)
It's assumed that A is the alias of t_mpnmst in the main query.
BTW, what is t_tnxhis ?


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

tried this and it gives the same result all dates and transactions still show up; Trying to just have the last transaction show up; t_tnshis is a transaction history table; any other suggestions


thanks

 

[willir]

Larry

I am jumping in a little late here -- Bob and PHV are extremely competent. ...But your post puzzled me.

You are looking for the most recent record for a manufacturer from three different tables.

Do you mean you have three tables, or three different records?

If you have three tables, if it is within you control, perhaps you may consider just using one table if it makes sense per the rules of "normalization". It would make this problem a heck of a lot easier, especially if you have to do this task on a monthly basis. Or is there a real good reason to use different tables?

Richard

 

[Basshopper]

I have 3 different tables(MAX MRP System) I am getting records from. The way our MRP system is layed out this was the only way to get the data needed. I need records from all 3 tables buy am interested in just looking at the last date a buy tranaction happened. The date and transaction data is in one table, the mfg part # is in another table and our internal part # is in all 3 tables which ties it all together. Hope that clarifies

 

[PHV]

[tt]Mfg # Description Std Cost Last Cost Last Qty Date
ECJ-1VB1C103K DIODE $0.00 $0.00 49 3/15/04
ECJ-1VB1C103K DIODE $0.00 $0.00 11 3/16/04[/tt]

Can you please post the SQL related to the output above to help us understand your 3 linked tables layout ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

Here it is

SELECT [t_mpnmst Max Linked].PRTNUM_49, [t_mpnmst Max Linked].MPNNUM_49, [t_prtmst Max Linked].PMDES1_01, [t_prtmst Max Linked].COST_01, [t_tnxhis Max Linked].COST_15, [t_tnxhis Max Linked].TNXQTY_15, [t_tnxhis Max Linked].TNXDTE_15
FROM ([t_mpnmst Max Linked] INNER JOIN [t_prtmst Max Linked] ON [t_mpnmst Max Linked].PRTNUM_49 = [t_prtmst Max Linked].PRTNUM_01) INNER JOIN [t_tnxhis Max Linked] ON [t_mpnmst Max Linked].PRTNUM_49 = [t_tnxhis Max Linked].PRTNUM_15
WHERE ((([t_tnxhis Max Linked].TNXCDE_15) Like "R"))
ORDER BY [t_mpnmst Max Linked].PRTNUM_49, [t_tnxhis Max Linked].TNXDTE_15 DESC;

 

[willir]

Larry

I think part of the answer lies in using DMAX. For one table the syntax is easy...

SELECT MRP1.*, MRP1.LastDate
FROM MRP1
WHERE (MRP1.LastDate)=DMax("[lastdate]","mrp1","[lastdate]")

Personally, I would use code to compare each record, but I will play and see if I can get an SQL statement to work...

Richard

 

[PHV]

To avoid complex subquery, create a named query, say qryLastDate:
SELECT PRTNUM_15, Max(TNXDTE_15) AS MaxOfTNXDTE_15
FROM [t_tnxhis Max Linked]
WHERE [t_tnxhis Max Linked].TNXCDE_15) Like "R"
GROUP BY PRTNUM_15;
Then amend your original query like this:
SELECT [t_mpnmst Max Linked].PRTNUM_49, [t_mpnmst Max Linked].MPNNUM_49, [t_prtmst Max Linked].PMDES1_01, [t_prtmst Max Linked].COST_01, [t_tnxhis Max Linked].COST_15, [t_tnxhis Max Linked].TNXQTY_15, [t_tnxhis Max Linked].TNXDTE_15
FROM ([t_mpnmst Max Linked] INNER JOIN [t_prtmst Max Linked] ON [t_mpnmst Max Linked].PRTNUM_49 = [t_prtmst Max Linked].PRTNUM_01) INNER JOIN (qryLastDate INNER JOIN [t_tnxhis Max Linked] ON qryLastDate.PRTNUM_15 = [t_tnxhis Max Linked].PRTNUM_15 AND qryLastDate.MaxOfTNXDTE_15 = [t_tnxhis Max Linked].TNXDTE_15) ON [t_mpnmst Max Linked].PRTNUM_49 = qryLastDate.PRTNUM_15
ORDER BY [t_mpnmst Max Linked].PRTNUM_49;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

Hi Richard; That worked like a dream. thank you very much for your help on this.

Larry

 

[willir]

Your are welcome. I still think you should have one table instead of three, but heh, you are the boss.