weekly query vs biweekly query - how to link up?

[snipe]

Hey guys,

I have the following 2 queries.


The top is based off paid wages and is biweekly. The bottom is based of time and attendance, and is weekly. The highlighted parts are for the same employee. Due to a legal issue, we have to look at everything weekly.

What I need to do is add in the missing weeks into the top query (the odd weeks); divide the wage amounts by 2 and use half for the odd week and half for the even week. Seems relatively simple, but I'm just flat out stuck.

I am well versed with VBA, but I have no idea how to use it to solve this problem. Any help would be greatly appreciated!

Thanks!

[CJ_London]

if I have understood correctly, create a table with two fields

WKE WKO
46....46
46....47
48....48
48....49
50....50
50....51
etc


then link this to your query on WKE = Week and then the values required would be divided by 2

Note that Year is a reserved word, using it can and will cause unexpected problems at some point - although surrounding with square brackets will often solve the problem a) why create a situation where you have to solve an unnecessary problem all the time, b) sometime square brackets don't solve the problem. Recommend you change it to something else

[InsuranceGuy]

You can also do this table-less. Something like this:

SELECT*FROM q_timecards_2
	,q_wages_1
WHERE q_timecards_2.[year] = q_wages_1.[year] AND format(INT(q_timecards_2.[week])-INT(qry_timecards_2.[week]) mod 2, "00")= q_wages_1.[week]

[CJ_London]

good thought - though you could actually have a join on year and would also need to link on employee