would this work please ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

would this work please ?

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-02-28 : 20:23:19

I am trying to update the following [zzzExtra Tran Details]
table based on several inner join
conditions but I need a flexible date range (with no time in the field) as 1 day either side of (trans date = date) ie :

if all the non date join conditions are true
and [_EFT Records from Old System].Date = 31/01/2007
and [zzzExtra Tran Details].trans_date = 30/01/2007
or conversley if [_EFT Records from Old System].Date = 30/01/2007 and [zzzExtra Tran Details].trans_date = 31/01/2007
then I want the update to take affect.

Could someone please advise if the following
code in red would provide me with this
flexibility please :

UPDATE [zzzExtra Tran Details] INNER JOIN [_EFT Records from Old System] ON ([zzzExtra Tran Details].store_name = [_EFT Records from Old System].[Store Name]) AND ([zzzExtra Tran Details].[Sequence Nr] = [_EFT Records from Old System].[Sequence Number]) AND ([zzzExtra Tran Details].amount = [_EFT Records from Old System].Amount) AND ([zzzExtra Tran Details].trans_date = [_EFT Records from Old System].Date OR [zzzExtra Tran Details].trans_date -1 = [_EFT Records from Old System].Date OR [zzzExtra Tran Details].trans_date = [_EFT Records from Old System].Date - 1) AND ([zzzExtra Tran Details].[Account Nr] = [_EFT Records from Old System].[Account Nr]) SET [_EFT Records from Old System].[Till trans_time] = [zzzExtra Tran Details]![trans_time], [_EFT Records from Old System].[Till trans_no] = [zzzExtra Tran Details]![trans_no], [_EFT Records from Old System].registration = [zzzExtra Tran Details]![registration], [_EFT Records from Old System].product_desc = [zzzExtra Tran Details]![product_desc], [_EFT Records from Old System].price_litre = [zzzExtra Tran Details]![price_litre], [_EFT Records from Old System].litres = [zzzExtra Tran Details]![litres], [_EFT Records from Old System].[Fuel Amount] = [zzzExtra Tran Details]![Fuelvalue]
WHERE ((([_EFT Records from Old System].[Till trans_time]) Is Null));

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-28 : 22:52:28

reformatted so I don't have to gouge my eyes out


UPDATE [zzzExtra Tran Details] 
INNER JOIN [_EFT Records from Old System] 
ON ([zzzExtra Tran Details].store_name                       = [_EFT Records from Old System].[Store Name]) 
    AND ([zzzExtra Tran Details].[Sequence Nr]               = [_EFT Records from Old System].[Sequence Number]) 
    AND ([zzzExtra Tran Details].amount                      = [_EFT Records from Old System].Amount) 
    AND ([zzzExtra Tran Details].trans_date                  = [_EFT Records from Old System].Date 
     OR  [zzzExtra Tran Details].trans_date -1               = [_EFT Records from Old System].Date 
     OR  [zzzExtra Tran Details].trans_date                  = [_EFT Records from Old System].Date - 1) 
    AND ([zzzExtra Tran Details].[Account Nr]                = [_EFT Records from Old System].[Account Nr]) 
SET [_EFT Records from Old System].[Till trans_time]         = [zzzExtra Tran Details]![trans_time], 
    [_EFT Records from Old System].[Till trans_no]           = [zzzExtra Tran Details]![trans_no], 
    [_EFT Records from Old System].registration              = [zzzExtra Tran Details]![registration], 
    [_EFT Records from Old System].product_desc              = [zzzExtra Tran Details]![product_desc], 
    [_EFT Records from Old System].price_litre               = [zzzExtra Tran Details]![price_litre], 
    [_EFT Records from Old System].litres                    = [zzzExtra Tran Details]![litres], 
    [_EFT Records from Old System].[Fuel Amount]              = [zzzExtra Tran Details]![Fuelvalue] 
WHERE ((([_EFT Records from Old System].[Till trans_time]) Is Null));

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-28 : 23:05:57

ok, I don't think that is going to work for you. the INNER JOIN is going to limit the rows coming back not make for a flexible date range that you want.

Have you tried making the date ranges part of your WHERE clause instead?

-ec

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-03-01 : 04:34:48

cheers eyechart
i think I have not explained myself clearly enough then, the reason I am doing the inner join as per above is to perform a join on the condition of the date being 1 date different from the trans_date and vice versa, I dont think the WHERE clause will do this as only the records that have : ((([_EFT Records from Old System].[Till trans_time]) Is Null)) are wanting to get updated.

What about if I first update all records haveing the same date and then create a second query that looks like this will this work :

UPDATE [zzzExtra Tran Details]
INNER JOIN [_EFT Records from Old System]
ON ([zzzExtra Tran Details].store_name = [_EFT Records from Old System].[Store Name])
AND ([zzzExtra Tran Details].[Sequence Nr] = [_EFT Records from Old System].[Sequence Number])
AND ([zzzExtra Tran Details].amount = [_EFT Records from Old System].Amount)
AND ([zzzExtra Tran Details].trans_date -1 = [_EFT Records from Old System].Date )
AND ([zzzExtra Tran Details].[Account Nr] = [_EFT Records from Old System].[Account Nr])
SET [_EFT Records from Old System].[Till trans_time] = [zzzExtra Tran Details]![trans_time],
[_EFT Records from Old System].[Till trans_no] = [zzzExtra Tran Details]![trans_no],
[_EFT Records from Old System].registration = [zzzExtra Tran Details]![registration],
[_EFT Records from Old System].product_desc = [zzzExtra Tran Details]![product_desc],
[_EFT Records from Old System].price_litre = [zzzExtra Tran Details]![price_litre],
[_EFT Records from Old System].litres = [zzzExtra Tran Details]![litres],
[_EFT Records from Old System].[Fuel Amount] = [zzzExtra Tran Details]![Fuelvalue]
WHERE ((([_EFT Records from Old System].[Till trans_time]) Is Null));

and then a third that looks like this :

UPDATE [zzzExtra Tran Details]
INNER JOIN [_EFT Records from Old System]
ON ([zzzExtra Tran Details].store_name = [_EFT Records from Old System].[Store Name])
AND ([zzzExtra Tran Details].[Sequence Nr] = [_EFT Records from Old System].[Sequence Number])
AND ([zzzExtra Tran Details].amount = [_EFT Records from Old System].Amount)
AND ([zzzExtra Tran Details].trans_date = [_EFT Records from Old System].Date - 1 )
AND ([zzzExtra Tran Details].[Account Nr] = [_EFT Records from Old System].[Account Nr])
SET [_EFT Records from Old System].[Till trans_time] = [zzzExtra Tran Details]![trans_time],
[_EFT Records from Old System].[Till trans_no] = [zzzExtra Tran Details]![trans_no],
[_EFT Records from Old System].registration = [zzzExtra Tran Details]![registration],
[_EFT Records from Old System].product_desc = [zzzExtra Tran Details]![product_desc],
[_EFT Records from Old System].price_litre = [zzzExtra Tran Details]![price_litre],
[_EFT Records from Old System].litres = [zzzExtra Tran Details]![litres],
[_EFT Records from Old System].[Fuel Amount] = [zzzExtra Tran Details]![Fuelvalue]
WHERE ((([_EFT Records from Old System].[Till trans_time]) Is Null));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources