Connect two queries 2

JoPaBC · Jun 27, 2019

Hi,

I have two queries:
VAC_TIME_BY_YEAR (calculates earned vacation pay)

SQL:

SELECT dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID, dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION, dbo_EE_T.EE_F_SUR AS LAST_NAME, dbo_EE_T.EE_F_FIRST AS FIRST_NAME, Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED, Sum([dbo_TIME_T].[TIME_F_AMOUNT])*0.04 AS VACPAY
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:]) AND ((dbo_TIME_T.TIME_F_DIV_NUM)=2) AND ((dbo_TIME_T.TIME_F_PAY_CODE)<499 And (dbo_TIME_T.TIME_F_PAY_CODE)<>303) AND ((dbo_TIME_T.TIME_F_EMP_NUM)=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY dbo_TIME_T.TIME_F_EMP_NUM, dbo_TIME_T.TIME_F_DIV_NUM, dbo_EE_T.EE_F_SUR, dbo_EE_T.EE_F_FIRST
ORDER BY dbo_EE_T.EE_F_SUR;

and
VAC_TIME_PAID_BY_YEAR (calculates already paid vacation pay)

SQL:

SELECT [dbo_TIME_T].[TIME_F_EMP_NUM] AS EMP_ID, [dbo_TIME_T].[TIME_F_DIV_NUM] AS DIVISION, [dbo_EE_T].[EE_F_SUR] AS LAST_NAME, [dbo_EE_T].[EE_F_FIRST] AS FIRST_NAME, Sum([dbo_TIME_T].[TIME_F_AMOUNT]) AS VAC_PAID
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:]) And (([dbo_TIME_T].[TIME_F_DIV_NUM])=2) And (([dbo_TIME_T].[TIME_F_PAY_CODE])=303) And (([dbo_TIME_T].[TIME_F_EMP_NUM])=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY [dbo_TIME_T].[TIME_F_EMP_NUM], [dbo_TIME_T].[TIME_F_DIV_NUM], [dbo_EE_T].[EE_F_SUR], [dbo_EE_T].[EE_F_FIRST]
ORDER BY dbo_EE_T.EE_F_SUR;

I tried to combine output from both so it would show earned vacation pay, already paid vacation pay (zero if nothing paid yet), vacation pay to be paid (difference between those two), like in this Excel

All my attempts failed saying 'fields from record sources can't connect' even I created and saved relationship between those two (EMP_ID).

Is there any way to do as query (or report) instead copy and do it manually in Excel?
Thanks

kray4660 · Jun 27, 2019

Normally I would do a union between the two queries. You will have to match column by column (you have an extra column in the first query that will need to be addressed). So something like this. Been a while since I have done unions in Access so, I might something amiss.

SELECT
dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID
, dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION
, dbo_EE_T.EE_F_SUR AS LAST_NAME
, dbo_EE_T.EE_F_FIRST AS FIRST_NAME
, Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED
, Sum([dbo_TIME_T].[TIME_F_AMOUNT])*0.04 AS VACPAY
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:])
AND ((dbo_TIME_T.TIME_F_DIV_NUM)=2) AND ((dbo_TIME_T.TIME_F_PAY_CODE)<499 And (dbo_TIME_T.TIME_F_PAY_CODE)<>303) AND ((dbo_TIME_T.TIME_F_EMP_NUM)=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY dbo_TIME_T.TIME_F_EMP_NUM, dbo_TIME_T.TIME_F_DIV_NUM, dbo_EE_T.EE_F_SUR, dbo_EE_T.EE_F_FIRST

union

SELECT [dbo_TIME_T].[TIME_F_EMP_NUM] AS EMP_ID
, [dbo_TIME_T].[TIME_F_DIV_NUM] AS DIVISION
, [dbo_EE_T].[EE_F_SUR] AS LAST_NAME
, [dbo_EE_T].[EE_F_FIRST] AS FIRST_NAME
, 0
, Sum([dbo_TIME_T].[TIME_F_AMOUNT]) AS VAC_PAID
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:])
And (([dbo_TIME_T].[TIME_F_DIV_NUM])=2) And (([dbo_TIME_T].[TIME_F_PAY_CODE])=303) And (([dbo_TIME_T].[TIME_F_EMP_NUM])=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY [dbo_TIME_T].[TIME_F_EMP_NUM], [dbo_TIME_T].[TIME_F_DIV_NUM], [dbo_EE_T].[EE_F_SUR], [dbo_EE_T].[EE_F_FIRST]
ORDER BY dbo_EE_T.EE_F_SUR;

JoPaBC · Jul 2, 2019

Hi, thanks for your input; your query runs without any error (after removing ORDER BY)

SQL:

SELECT
dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID
, dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION
, dbo_EE_T.EE_F_SUR AS LAST_NAME
, dbo_EE_T.EE_F_FIRST AS FIRST_NAME
, Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED
, Sum([dbo_TIME_T].[TIME_F_AMOUNT])*0.04 AS VACPAY
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:])
AND ((dbo_TIME_T.TIME_F_DIV_NUM)=2) AND ((dbo_TIME_T.TIME_F_PAY_CODE)<499 And (dbo_TIME_T.TIME_F_PAY_CODE)<>303) AND ((dbo_TIME_T.TIME_F_EMP_NUM)=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY dbo_TIME_T.TIME_F_EMP_NUM, dbo_TIME_T.TIME_F_DIV_NUM, dbo_EE_T.EE_F_SUR, dbo_EE_T.EE_F_FIRST
union
SELECT [dbo_TIME_T].[TIME_F_EMP_NUM] AS EMP_ID
, [dbo_TIME_T].[TIME_F_DIV_NUM] AS DIVISION
, [dbo_EE_T].[EE_F_SUR] AS LAST_NAME
, [dbo_EE_T].[EE_F_FIRST] AS FIRST_NAME
, 0
, Sum([dbo_TIME_T].[TIME_F_AMOUNT]) AS VAC_PAID
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:])
And (([dbo_TIME_T].[TIME_F_DIV_NUM])=2) And (([dbo_TIME_T].[TIME_F_PAY_CODE])=303) And (([dbo_TIME_T].[TIME_F_EMP_NUM])=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY [dbo_TIME_T].[TIME_F_EMP_NUM], [dbo_TIME_T].[TIME_F_DIV_NUM], [dbo_EE_T].[EE_F_SUR], [dbo_EE_T].[EE_F_FIRST]

but it I am trying to add another field (column) rather than another row.
Thanks anyway.

Andrzejek · Jul 3, 2019

Just my $0.02...

You may consider using [blue]aliases[/blue] for your tables:
[tt]FROM dbo_TIME_T [blue]T[/blue], dbo_EE_T [blue]E[/blue][/tt], it makes reading easier, IMHO

Also, since there are no spaces in your fields' names and you don't use any reserved words either (Nice!), you don't really need [] around the fields' names.

The only field that needs an indication of which table it is coming from is [tt]TIME_F_PAY_PERIOD[/tt]

So your statement may just look like this:

Code:

SELECT
  T.TIME_F_EMP_NUM AS EMP_ID
, T.TIME_F_DIV_NUM AS DIVISION
, E.EE_F_SUR       AS LAST_NAME
, E.EE_F_FIRST     AS FIRST_NAME
, Sum(T.TIME_F_AMOUNT)      AS EARNED
, Sum(T.TIME_F_AMOUNT)*0.04 AS VACPAY
FROM dbo_TIME_T T, dbo_EE_T E
WHERE (((Left(TIME_F_PAY_PERIOD,4))=[ENTER YEAR:])
AND (T.TIME_F_DIV_NUM = 2) 
AND (T.TIME_F_PAY_CODE < 499 And T.TIME_F_PAY_CODE <> 303) 
AND (T.TIME_F_EMP_NUM = E.EE_FP_EMP_NUM))
GROUP BY T.TIME_F_EMP_NUM, T.TIME_F_DIV_NUM, E.EE_F_SUR, E.EE_F_FIRST
union
SELECT 
  T.TIME_F_EMP_NUM AS EMP_ID
, T.TIME_F_DIV_NUM AS DIVISION
, E.EE_F_SUR       AS LAST_NAME
, E.EE_F_FIRST     AS FIRST_NAME
, 0
, Sum(T.TIME_F_AMOUNT) AS VAC_PAID
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left(TIME_F_PAY_PERIOD,4))=[ENTER YEAR:])
And (T.TIME_F_DIV_NUM  = 2) 
And (T.TIME_F_PAY_CODE = 303) 
And (T.TIME_F_EMP_NUM  = E.[EE_FP_EMP_NUM]))
GROUP BY T.TIME_F_EMP_NUM, T.TIME_F_DIV_NUM, E.EE_F_SUR, E.EE_F_FIRST

BTW - you do know that aliases for fields matter only in first SELECT, all other fields' aliases in UNION are ignored. But it is nice to named them the same so you know which field goes where. I do the same.

---- Andy

There is a great need for a sarcasm font.

zandsc1 · Jul 3, 2019

Good suggestions from Andrzejek but I think you're trying to get all the data into columns with joins instead of unions. If so - subqueries are your friend:

SQL:

SELECT
	dbo_EE_T.TIME_F_EMP_NUM AS EMP_ID,
	dbo_EE_T.TIME_F_DIV_NUM AS DIVISION,
	dbo_EE_T.EE_F_SUR AS LAST_NAME,
	dbo_EE_T.EE_F_FIRST AS FIRST NAME,
	n.EARNED,
	n.VACPAY,
	nz(m.PAID_VAC,0) as VAC_PAID
FROM
	dbo_EE_T LEFT JOIN
	(SELECT
		dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
		Sum(T.TIME_F_AMOUNT) AS PAID_VAC
	FROM 
		dbo_TIME_T 
	WHERE 
		Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:]
		AND dbo_TIME_T.TIME_F_DIV_NUM = 2
		AND dbo_TIME_T.TIME_F_PAY_CODE = 303
	GROUP BY 
		dbo_TIME_T.TIME_F_EMP_NUM, 
		dbo_TIME_T.TIME_F_DIV_NUM) m ON dbo_EE_T.EE_F_EMP_NUM = m.TIME_F_EMP_NUM,
	(SELECT
		dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
		Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED,
		Sum(dbo_TIME_T.TIME_F_AMOUNT)*0.04 AS VACPAY,
	FROM 
		dbo_TIME_T 
	WHERE 
		Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:]
		AND dbo_TIME_T.TIME_F_DIV_NUM = 2
		AND dbo_TIME_T.TIME_F_PAY_CODE < 499 
		AND dbo_TIME_T.TIME_F_PAY_CODE <> 303
	GROUP BY 
		dbo_TIME_T.TIME_F_EMP_NUM, 
		dbo_TIME_T.TIME_F_DIV_NUM) n
WHERE
	dbo_EE_T.EE_F_EMP_NUM = n.TIME_F_EMP_NUM AND

Note that I left joined the 'm' subquery since as you said it's possible there won't be an entry for that employee if they haven't taken any time off. This does, however, assume they've accrued some time so you might run into issues still if you run it very early in the year before any time is accrued or for new employees. I also added the Nz() around paid vacation so it will return 0 instead of null if there are no entries from the left join. I also got rid of the multiple instances of dbo_EE_T -- you don't actually need it in the subqueries, just link it in at the top level. Hope this helps.

Edit - typos in the SQL.

JoPaBC · Jul 3, 2019

Thanks, zandsc1, you are right; yes, Andrzejek made valid suggestions, but I am trying to get all the data into columns with joins instead of unions.

I tried your query (with updated typo)

SQL:

SELECT
dbo_EE_T.TIME_F_EMP_NUM AS EMP_ID,
dbo_EE_T.TIME_F_DIV_NUM AS DIVISION,
dbo_EE_T.EE_F_SUR AS LAST_NAME,
dbo_EE_T.EE_F_FIRST AS FIRST_NAME,
n.EARNED,
n.VACPAY,
nz(m.PAID_VAC,0) as VAC_PAID
FROM
dbo_EE_T LEFT JOIN
(SELECT
dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
Sum(T.TIME_F_AMOUNT) AS PAID_VAC
FROM 
dbo_TIME_T 
WHERE 
Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:]
AND dbo_TIME_T.TIME_F_DIV_NUM = 2
AND dbo_TIME_T.TIME_F_PAY_CODE = 303
GROUP BY 
dbo_TIME_T.TIME_F_EMP_NUM, 
dbo_TIME_T.TIME_F_DIV_NUM) m ON dbo_EE_T.EE_F_EMP_NUM = m.TIME_F_EMP_NUM,
(SELECT
dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED,
Sum(dbo_TIME_T.TIME_F_AMOUNT)*0.04 AS VACPAY,
FROM 
dbo_TIME_T 
WHERE 
Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:]
AND dbo_TIME_T.TIME_F_DIV_NUM = 2
AND dbo_TIME_T.TIME_F_PAY_CODE < 499 
AND dbo_TIME_T.TIME_F_PAY_CODE <> 303
GROUP BY 
dbo_TIME_T.TIME_F_EMP_NUM, 
dbo_TIME_T.TIME_F_DIV_NUM) n
WHERE
dbo_EE_T.EE_F_EMP_NUM = n.TIME_F_EMP_NUM

but I got this error message 'Join expression not supported'

zandsc1 · Jul 3, 2019

Hmmm I know that Access is a bit more limited than SQL databases in terms of join capabilities but it should be able to do a simple left join like this. Your two tables have proprietary data in them so I won't ask you to post them, but can you give the SQL definitions or screen captures of them in design mode? I might be able to sleuth it out if I have those.

JoPaBC · Jul 3, 2019

Yes, sure here are the screen captures; these tables are SQL Express and MS Access is used as a front end.

Thanks

zandsc1 · Jul 3, 2019

Thanks. I had to nest the left join into the subquery to get it to run for some reason. This runs without error on my system. See if it gets the results you're looking for:

SQL:

SELECT
	m.EE_FP_EMP_NUM as EMP_ID,
	m.EE_F_DIV_NUM as DIVISION,
	dbo_EE_T.EE_F_SUR AS LAST_NAME,
	dbo_EE_T.EE_F_FIRST AS FIRST_NAME,
	n.EARNED,
	n.VACPAY,
	nz(m.PAID_VAC,0) as VAC_PAID
FROM
	dbo_EE_T, 
	(SELECT
		dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
		dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION,
		Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED,
		Sum(dbo_TIME_T.TIME_F_AMOUNT)*0.04 AS VACPAY
	FROM 
		dbo_TIME_T 
	WHERE 
		Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:]
		AND dbo_TIME_T.TIME_F_DIV_NUM = 2
		AND dbo_TIME_T.TIME_F_PAY_CODE < 499 
		AND dbo_TIME_T.TIME_F_PAY_CODE <> 303
	GROUP BY 
		dbo_TIME_T.TIME_F_EMP_NUM, 
		dbo_TIME_T.TIME_F_DIV_NUM) n,
	(SELECT 
		dbo_EE_T.EE_FP_EMP_NUM, 
		dbo_EE_T.EE_F_DIV_NUM,
		sum(dbo_TIME_T.TIME_F_AMOUNT) as PAID_VAC
	FROM 
		dbo_EE_T LEFT JOIN 
		dbo_TIME_T ON dbo_EE_T.EE_FP_EMP_NUM = dbo_TIME_T.TIME_F_EMP_NUM
	WHERE 
		Left(TIME_F_PAY_PERIOD,4)=[ENTER YEAR:] AND
		dbo_EE_T.EE_F_DIV_NUM=2 AND 
		dbo_TIME_T.TIME_F_PAY_CODE=303
	GROUP BY 
		EE_FP_EMP_NUM, 
		EE_F_DIV_NUM) m
WHERE
	dbo_EE_T.EE_FP_EMP_NUM = n.EMP_ID AND
	n.EMP_ID = m.EE_FP_EMP_NUM

JoPaBC · Jul 3, 2019

Thank you, that's almost exactly what I try to do, except it shows only persons who already got paid right now.

JoPaBC · Jul 4, 2019

It is in the format I want, but it doesn't show records if vacation not paid yet.

JoPaBC · Jul 6, 2019

Well, it looks like it is working now; I did it in three queries - one for vac pay earned, one for vac pay paid and one to combine both:

Earned:

Code:

SELECT dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID, dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION, dbo_EE_T.EE_F_SUR AS LAST_NAME, dbo_EE_T.EE_F_FIRST AS FIRST_NAME, Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED, Sum([dbo_TIME_T].[TIME_F_AMOUNT])*0.04 AS VACPAY
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:]) AND ((dbo_TIME_T.TIME_F_DIV_NUM)=2) AND ((dbo_TIME_T.TIME_F_PAY_CODE)<499 And (dbo_TIME_T.TIME_F_PAY_CODE)<>303) AND ((dbo_TIME_T.TIME_F_EMP_NUM)=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY dbo_TIME_T.TIME_F_EMP_NUM, dbo_TIME_T.TIME_F_DIV_NUM, dbo_EE_T.EE_F_SUR, dbo_EE_T.EE_F_FIRST
ORDER BY dbo_EE_T.EE_F_SUR;

Paid:

Code:

SELECT [dbo_TIME_T].[TIME_F_EMP_NUM] AS EMP_ID, [dbo_TIME_T].[TIME_F_DIV_NUM] AS DIVISION, [dbo_EE_T].[EE_F_SUR] AS LAST_NAME, [dbo_EE_T].[EE_F_FIRST] AS FIRST_NAME, Sum([dbo_TIME_T].[TIME_F_AMOUNT]) AS VAC_PAID
FROM dbo_TIME_T, dbo_EE_T
WHERE (((Left([TIME_F_PAY_PERIOD],4))=[ENTER YEAR:]) And (([dbo_TIME_T].[TIME_F_DIV_NUM])=2) And (([dbo_TIME_T].[TIME_F_PAY_CODE])=303) And (([dbo_TIME_T].[TIME_F_EMP_NUM])=[dbo_EE_T].[EE_FP_EMP_NUM]))
GROUP BY [dbo_TIME_T].[TIME_F_EMP_NUM], [dbo_TIME_T].[TIME_F_DIV_NUM], [dbo_EE_T].[EE_F_SUR], [dbo_EE_T].[EE_F_FIRST]
ORDER BY dbo_EE_T.EE_F_SUR;

Show both:

Code:

SELECT [BEPAID].[EMP_ID], [BEPAID].[DIVISION], [BEPAID].[LAST_NAME], [BEPAID].[FIRST_NAME], [BEPAID].[EARNED], [BEPAID].[VACPAY], [PAID].[VAC_PAID]
FROM BEPAID LEFT JOIN PAID ON [BEPAID].[EMP_ID]=[PAID].[EMP_ID];

It is like 90% I wanted; would be nice to show zeroes if vac pay not paid yet and add another column to the third query with 'value = earned - paid', but I will try to do it in a report.

zandsc1 · Jul 8, 2019

Access is doing some odd things here in terms of what it will and will not allow with left joining subqueries. I loaded up some data into tables to try and match your setup and played around with it until I got the results I wanted. This should give you everything you were asking for, including a 0 value when no vacation has been taken and a VAC_BALANCE field that is the balance between earned and taken vacation.

SQL:

SELECT
	m.EE_FP_EMP_NUM as EMP_ID,
	m.EE_F_DIV_NUM as DIVISION,
	dbo_EE_T.EE_F_SUR AS LAST_NAME,
	dbo_EE_T.EE_F_FIRST AS FIRST_NAME,
	n.EARNED,
	n.VACPAY,
	nz(m.PAID_VAC,0) as VAC_PAID,
	n.VACPAY-nz(m.PAID_VAC,0) AS VAC_BALANCE
FROM
	dbo_EE_T, 
	(SELECT
		dbo_TIME_T.TIME_F_EMP_NUM AS EMP_ID,
		dbo_TIME_T.TIME_F_DIV_NUM AS DIVISION,
		Sum(dbo_TIME_T.TIME_F_AMOUNT) AS EARNED,
		Sum(dbo_TIME_T.TIME_F_AMOUNT)*0.04 AS VACPAY
	FROM 
		dbo_TIME_T 
	WHERE 
		Year(TIME_F_PAY_PERIOD)=[ENTER YEAR:]
		AND dbo_TIME_T.TIME_F_DIV_NUM = 2
		AND dbo_TIME_T.TIME_F_PAY_CODE < 499 
		AND dbo_TIME_T.TIME_F_PAY_CODE <> 303
	GROUP BY 
		dbo_TIME_T.TIME_F_EMP_NUM, 
		dbo_TIME_T.TIME_F_DIV_NUM) n,
	(SELECT 
     		dbo_EE_T.EE_FP_EMP_NUM, 
     		dbo_EE_T.EE_F_DIV_NUM,
     		nz(sum(dbo_TIME_T.TIME_F_AMOUNT),0) as PAID_VAC
	FROM 
		      dbo_EE_T LEFT JOIN 
		      (SELECT * FROM dbo_TIME_T WHERE year(TIME_F_PAY_PERIOD) = [ENTER YEAR:] AND TIME_F_PAY_CODE = 303) n ON dbo_EE_T.EE_FP_EMP_NUM = n.TIME_F_EMP_NUM
	WHERE 
     		 dbo_EE_T.EE_F_DIV_NUM=2
	GROUP BY 
      		EE_FP_EMP_NUM, 
      		EE_F_DIV_NUM) m
WHERE
	dbo_EE_T.EE_FP_EMP_NUM = n.EMP_ID AND
	dbo_EE_T.EE_FP_EMP_NUM = m.EE_FP_EMP_NUM

Note: I changed your LEFT(date,4) to a YEAR(date) just to be sure you would get the year value you want even if it is stored in MM/DD/YYYY instead of YYYY-MM-DD.

Note 2: the NZ(value, value if null) function is great for handling null values!

JoPaBC · Jul 8, 2019

Thanks for your help; you are on the right track, it runs without error but looks like is a glitch since it returns an empty record set.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Connect two queries 2

JoPaBC

Technical User

kray4660

MIS

JoPaBC

Technical User

Andrzejek

Programmer

zandsc1

Programmer

JoPaBC

Technical User

zandsc1

Programmer

JoPaBC

Technical User

zandsc1

Programmer

JoPaBC

Technical User

JoPaBC

Technical User

JoPaBC

Technical User

zandsc1

Programmer

JoPaBC

Technical User

Similar threads

Part and Inventory Search

Sponsor