Need help!!! (1 Viewer)

[pablofonto]

I have a table that mutliple info per Object and I would like to show in one row the old and new information per Object. Basically the row that has an "end date" of 12/31/9999 is the new info and the row with end date different than 12/31/9999 is the old info. I think this can be doen with a query, but can't figure out how. Please take a look at my attached file to see what I would like to do.

Please help!

thanks,

pablofonto

 

[PeterF]

If there are only two records for each item the following query should work.

SELECT  YourTableName.[Object Type], 
        YourTableName.Object, 
        YourTableName.Infotype, 
        YourTableName.Sty, 
        YourTableName.[Changed 
ON     ], 
        YourTableName.User, 
        YourTableName.[Data field]   AS OldData, 
        YourTableName_1.[Data field] AS NewDate 
FROM    YourTableName 
INNER JOIN YourTableName AS YourTableName_1 
ON (YourTableName.Infotype          = YourTableName_1.Infotype) 
    AND (YourTableName.Object       = YourTableName_1.Object) 
WHERE (((YourTableName.[Start date])<[YourTableName_1].[Start date]));

 

[pablofonto]

Thank you Peter!
I will try this today and let you know. There might be times that we get more than 2 records per item. Will that make a difference with this code?

P

 

[pablofonto]

Thank you Peter, it worked great!!
I also tried with more than 2 records per item and still worked!

thanks for your help,

P

 

[pablofonto]

I need one more thing!

Thanks to Peter's help my query is almost working as I needed it to. The query is now displaying the OLD info under the OLD Info column and the new under the New Info column which is what I wanted. Now what I need is to have New Info display under the New Info column even if there is NOT old info for that record. Please see my attached document for more details.

thanks,

Pablofonto

 

[PeterF]

There should be a better way to do this in one query, what I came up with is a union query that appends the records that don't have changes jet.
Insert the following SQL after the SQL from my previous post and it should give you what you want.

UNION 

SELECT   FIRST(yourtablename.[object type]) AS [firstObject type], 
         yourtablename.OBJECT, 
         yourtablename.infotype, 
         FIRST(yourtablename.sty) AS firststy, 
         FIRST(yourtablename.[changed on]) AS [firstchanged on], 
         FIRST(yourtablename.USER) AS firstuser, 
         FIRST("") AS olddate, 
         FIRST(yourtablename.[data field]) AS [firstdata field] 
FROM     yourtablename 
GROUP BY yourtablename.OBJECT,yourtablename.infotype 
HAVING   (((COUNT(yourtablename.OBJECT)) = 1));

 

[pablofonto]

Thank you! I will try this today and let you know.

pablofonto

 

[pablofonto]

Peter, it didn't do anything! Am I missing anything"

SELECT [Activity Table4].Ob AS [Object type], [Activity Table4].ObjectID AS [Object ID], [Activity Table4].IT AS Infotype, [Activity Table4].Subty AS Sty, [Infotypes Desc].Description, [Activity Table4].[Changed on], [Activity Table4].[User name] AS [User], [Activity Table4].[Variable user data field] AS [Old Info], Infotypes.[Variable user data field] AS [New Info]
FROM ([Activity Table4] INNER JOIN [Activity Table] AS Infotypes ON ([Activity Table4].Ob = Infotypes.Ob) AND ([Activity Table4].Subty = Infotypes.Subty) AND ([Activity Table4].IT = Infotypes.IT)) LEFT JOIN [Infotypes Desc] ON ([Activity Table4].Subty = [Infotypes Desc].STY) AND ([Activity Table4].IT = [Infotypes Desc].IT)
WHERE ((([Activity Table4].[End date])<[Infotypes].[End date]))
ORDER BY [Activity Table4].ObjectID;
UNION
SELECT FIRST(Activity Table4.[object type]) AS [firstObject type],
Activity Table4.OBJECT,
Activity Table4.infotype,
FIRST(Activity Table4.sty) AS firststy,
FIRST(Activity Table4.[changed on]) AS [firstchanged on],
FIRST(Activity Table4.USER) AS firstuser,
FIRST("") AS olddate,
FIRST(Activity Table4.[data field]) AS [firstdata field]
FROM Activity Table4
GROUP BY Activity Table4.OBJECT,Activity Table4.infotype
HAVING (((COUNT(Activity Table4.OBJECT)) = 1));

 

[pablofonto]

Peter I figured out why was not working and it's okay, the only thing I can't figure out why for Objec 10010754 the Sty A002, A011 and B012 don't show. They should be display on the new data column.
I've attached the access file so you can see it. Thanks again for all your help!

Pablofonto

 

[PeterF]

The records are not shown in the query because the end dates are the same.

 

[pablofonto]

Is there a way to have those missing records show as well without compromising the other results? I would like these records to show as NEW INFO. I've never done anything this complicated in Access before and I'm not too familiar with SQL either, so I really appreciate all your help.

thanks,

Pablo