1

What I'm after is a summary of amount worked form base, using sql in MS Access, and this is what I want to do:

Sum(IIf(([Time Report].Destination) Like 'D-[Time Report].[Baselocation]',0)) AS [Total WorkFromBase in P2]

This doesn't work, as MS Access doesn't understand regex, and I need the 'D-' appended for it to match.

My options are as far as I'm aware:

  1. Learn and use VB macro(?) to get the pattern matching correct
  2. Use a complicated set of IIf statements, as sql doesn't have a native else if condition

I don't know VB, and I've only seen one example, which I couldn't make sense of.

If I go for a mass of IIfs then I have something like this

Sum(
    IIf(D-[Time Report].[Baselocation] = 'Base1', IIf(
        ([Time Report].Destination) = 'D-Base1',
    IIf(D-[Time Report].[Baselocation] = 'Base2', IIf(
        ([Time Report].Destination) = 'D-Base2',
    IIf(D-[Time Report].[Baselocation] = 'Base3a', IIf(
        ([Time Report].Destination) = 'D-Base3a' OR ([Time Report].Destination) = 'D-Base3b',
    IIf(D-[Time Report].[Baselocation] = 'Base3b', IIf(
        ([Time Report].Destination) = 'D-Base3a' OR ([Time Report].Destination) = 'D-Base3b',    ))
)) AS [Total Work From Base in P1],

Then I end up with a Syntax Error (Missing operator) type error,

So how can I match the two collumns, and sum when they're similar?

Community
  • 1
  • 1
AncientSwordRage
  • 7,086
  • 19
  • 90
  • 173

2 Answers2

1

Why not have a table of possibilities and reference that in your queries?

 If D-[Time Report].[Baselocation] = 'Base1' IN (SELECT Bases FROM NewTable)

Or 

 SELECT D-[Time Report].[Baselocation], NewTable.Destination 
 FROM D-[Time Report] 
 INNER JOIN Newtable
 ON D-[Time Report].[Baselocation] = NewTable.Bases

Where NewTable contains a list of locations and a location that that base maps to.

You can also use the LIKE or ALIKE operator:

IIf(D-[Time Report].[Baselocation] LIKE '*Base1*'

Some references:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • This seems like overkill, the new table is just a map of bases to locations, and are near enough the same except they have 'D-' prepended. Also I don't follow your variable names...table.Stuff? I've never used inner join before either, could you explain how you're using it please? – AncientSwordRage Aug 09 '12 at 13:14
  • I have added a different possibility which may suit you better. If you intend to continue using SQL, you need to do a bit of reading. You could make that query a lot simpler. BTW table.Stuff is just a note to indicate any field selected from a table. – Fionnuala Aug 09 '12 at 13:19
1

... I need the 'D-' appended for it to match.

You can concatenate "D-" to [Baselocation], then use an equals rather than Like comparison. 

[Time Report].Destination) = "D-" & [Time Report].[Baselocation]

But an IIf() expression includes 3 arguments.

IIf(expr, truepart, falsepart)

In your example, you supplied only 2 arguments, AFAICT. I think you want the Sum() to include some value ([amount worked] ?) when expr is True, but zero when expr is False. Maybe like this ...

Sum(IIf(([Time Report].Destination) = "D-" & [Time Report].[Baselocation], [amount worked, 0)) AS [Total WorkFromBase in P2]
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • This is wht I now have that works `Sum(IIf(([Time Report].Destination) = "D-" & [QRY7 Ready for Time Report].[Baselocation],[Time Report].[Hrs P1], 0)) AS [Total WorkFromBase in P1]` and `WHERE (([Time Report].Destination) = "D-" & [Time Report].[Baselocation])` after selecting so I don't get EveryThing with 'D-' in it. Otherwise. Perfect! – AncientSwordRage Aug 09 '12 at 14:42