0

Using an MS Access database with the following design:

Tables
Equipment, Employees, CreditCard

Fields
Equipment: ID, PrimaryEmployee, SecondaryEmployee
Employees: ID, CreditCard
CreditCard: ID, Number, Pin

So, a piece of equipment can have two different employees assigned to it. Each employee can have a CreditCard assigned to them, or not at all.

Based on the ID of a piece of Equipment:

  • If the piece of equipment has a SecondaryEmployeeID set, and that corresponding Employee has a CreditCard set, then return the value of that CreditCard.
  • Else If the piece of equipment has a PrimaryEmployeeID set, and that corresponding Employee has a CreditCard set, then return the value of that CreditCard.

I also have to pull the employee's name from the Employees table in the same query, and thus, I was using a LEFT JOIN before, which seemed to make it impossible to do this in my knowledge.

EDITED

My current query attempt:

SELECT
    Equipment.ID,
    Equipment.PrimaryEmployee,
    Equipment.SecondaryEmployee,
    Employees.CreditCard,
    CreditCard.Pin
FROM
    (Equipment
        LEFT JOIN Employees ON Equipment.PrimaryEmployee = Employees.ID
    )
LEFT JOIN CreditCard ON Employees.CreditCard = CreditCard.ID
WHERE (((Equipment.EquipmentType)=1))

I removed some unrelated fields from the query to hopefully improve readability, and simplify the area I'm having troubles.

This is currently generating a result like:

ID    PrimaryEmployee    SecondaryEmployee    CreditCard    Pin
--------------------------------------------------------------------
1     John Doe           Jack Smith           1234567890    1234
2     Bubba Smith        Ryan Howard          2345678901    2345

The problem is that it only matches on the PrimaryEmployee. I need it to check the SecondaryEmployee first, and if that doesn't have a match or credit card set, then match on the PrimaryEmployee. If neither match, then return Null or '-'.

Let me know if additional information is still required.

crush
  • 15,889
  • 8
  • 54
  • 95
  • have you written any query yourself? if so please post it. – Thakur Apr 17 '12 at 19:26
  • 2
    Using the query design window, add your table(s) and any necessary joins. Add the field you want. Fiddle around until you get very, very stuck, then post the sql from SQL View. – Fionnuala Apr 17 '12 at 19:27
  • Added the SQL and the resulting tables. – crush Apr 17 '12 at 19:37
  • 5
    You are storing credit card numbers and their pin in an Access database!!! =:-o – Tony Apr 17 '12 at 19:56
  • Yes, I know. Mandated by boss. This is all on the internal intranet, not that that makes it much safer. I tried to get them to employ different techniques...Not a fan at all of Access. – crush Apr 17 '12 at 20:07
  • Your boss might like to read http://www.di-mgt.com.au/cryptoCreditcard.html and http://stackoverflow.com/questions/1220751/how-to-choose-an-aes-encryption-mode-cbc-ecb-ctr-ocb-cfb, both obtained from a very quick search. – Fionnuala Apr 17 '12 at 20:36
  • Thanks for the links. I doubt my boss will be swayed, since he has no business directing the development of anything IT related, but still good reading, nonetheless. – crush Apr 17 '12 at 20:55

2 Answers2

1

The easiest way to do this, without mucking up the SQL, is to create a second query that uses the results from your first query and pulls in the Employee column where your first result IS NULL.

Once you've got your two queries, you're just a stone's throw from writing a third query that joins the results from both queries into a single output.

Daniel Szabo
  • 6,926
  • 5
  • 44
  • 61
  • Thanks! Not sure why that slipped my mind as I've done that before with 5-6 queries even...this will accomplish exactly what I need, though. Guess because Access confuses me, with it's capabilities still... I like working with raw SQL/C++ or PHP heh – crush Apr 17 '12 at 19:55
  • @crush: I'm totally with you on that. If I had some free time, I'd probably pick at it to see if I could do it...but I guess in my old age, I'm just more interested in getting results and moving on ;) – Daniel Szabo Apr 17 '12 at 20:08
0
LEFT JOIN Employees ON ISNULL(Equipment.SecondaryEmployee, Equipment.PrimaryEmployee) = Employees.ID

This should work, but check the performance. The left join will pull nulls for ones without either employee.

Russell Fox
  • 4,894
  • 1
  • 21
  • 25
  • Apparently, it needs to work like IIF(IsNull(Equipment.SecondaryEmployee), Equipment.PrimaryEmployee, Equipment.SecondaryEmployee) – crush Apr 17 '12 at 20:00
  • MS Access' ISNULL() accepts only one argument. Perhaps the syntax you read was for SQL Server ISNULL(), which does accept 2. If you want an Access function similar to SQL Server ISNULL(), look at Nz(). However I don't know whether it will work in a join condition. – HansUp Apr 17 '12 at 20:03
  • Yes, sorry, I'm in SQL Server land: try the nz() function in the join in place of my ISNULL() - I think that will work: – Russell Fox Apr 17 '12 at 20:21