Query Related Field (1 Viewer)

X

xyba

Registered User.
Local time
Today, 20:26
Joined
Jan 28, 2016
Messages
189
Hi

I have a form that includes 6 fields in a column for department name. Next to each of those is a field that stores the name of a staff member in that department.

In a related query I call a module function that looks at the 6 department fields and returns the value of the last non null field. This means, in the real world, that that is the current department.

I also need to identify, in the query, where the corresponding staff name field is blank.

In a nutshell, I am wanting my query/report to identify all records that are currently in Department X but no staff assigned.

Hopefully someone on here can help?

Thanks.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:26
Joined
May 7, 2009
Messages
19,230
Can you post the table structure. And its cobtent.
 
X

xyba

Registered User.
Local time
Today, 20:26
Joined
Jan 28, 2016
Messages
189
arnelgp said:
Can you post the table structure. And its cobtent.
Click to expand...

Table attached. Fields Referral1 through to Referral6 are the department fields and AARef1 to AARef6 are the staff name fields.

I'm aware it doesn't fit normaliztion standards but this is a quick interim fix whilst my company is awaiting its new CRM.
 

Attachments

  • eXStarts.xlsx
    115.3 KB · Views: 37
X

xyba

Registered User.
Local time
Today, 20:26
Joined
Jan 28, 2016
Messages
189
arnelgp said:
Can you post the table structure. And its cobtent.
Click to expand...

And this is the module to find the current department...

Code: 
Option Compare Database

Option Explicit

Public Function fnLastLoc(ParamArray ref() As Variant) As Variant
Dim v As Variant
Dim i As Integer
Dim pos As Long
For i = UBound(ref) To 0 Step -1
    v = v & (IIf(Trim(ref(i) & "") = "", Null, ref(i)) + "/")
Next
pos = InStr(v, "/")
If (pos <> 0) Then
    fnLastLoc = Left(v, pos - 1)
Else
    fnLastLoc = ref(0)
End If


End Function
 
P

plog

Banishment Pending
Local time
Today, 14:26
Joined
May 11, 2011
Messages
11,645
I'm aware it doesn't fit normaliztion standards but this is a quick interim fix
Click to expand...

Is it really a quick fix when you have to build (and ask advice on) these big hacks around your improper table structure? I mean this issue is solved with one simple query if you properly structure your tables.
 
X

xyba

Registered User.
Local time
Today, 20:26
Joined
Jan 28, 2016
Messages
189
plog said:
Is it really a quick fix when you have to build (and ask advice on) these big hacks around your improper table structure? I mean this issue is solved with one simple query if you properly structure your tables.
Click to expand...

In this circumstance, yes, when I had around two hours to throw something together. I was going for just an excel spreadsheet, but the ease of input and multi-user accessibility were key so I had to opt for a db.

Not great but this is the only issue I've had with it as all other reports are created and working. I could export the table to excel and report that way but if this can be solved it's preferable.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:26
Joined
May 7, 2009
Messages
19,230
i cant grasp the idea but maybe a Union Query
will do it for you.

SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral1 As Department, eStarts.AARef1 As Staff
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral2, eStarts.AARef2
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral3, eStarts.AARef3
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral4, eStarts.AARef4
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral5, eStarts.AARef5
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral6, eStarts.AARef6
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
 
X

xyba

Registered User.
Local time
Today, 20:26
Joined
Jan 28, 2016
Messages
189
arnelgp said:
i cant grasp the idea but maybe a Union Query
will do it for you.

SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral1 As Department, eStarts.AARef1 As Staff
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral2, eStarts.AARef2
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral3, eStarts.AARef3
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral4, eStarts.AARef4
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral5, eStarts.AARef5
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
UNION ALL
SELECT eStarts.ID, eStarts.FirstName, eStarts.LastName, eStarts.Referral6, eStarts.AARef6
FROM eStarts
WHERE ((Not (eStarts.ID) Is Null))
Click to expand...

That worked great. Thanks for your help and time.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:26
Joined
May 7, 2009
Messages
19,230
You're welcome
 
You must log in or register to reply here.

Similar threads

N
Enter Parameter Value Error
Replies
11
Views
349
Gasman
Gasman
Z
Pivot SQL SERVER
Replies
3
Views
323
zezo2021
Z
C
Query to include "X Criteria", but not exclude other criteria in same field associated with Employee
2
Replies
21
Views
759
Pat Hartman
P
azhar2006
Error in the query statement.
Replies
4
Views
431
The_Doc_Man
The_Doc_Man
M
How to populate a junction table? Take 2
Replies
7
Views
368
Missomissou
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom