Query to Crosswalk Two Fields

Question

Hi

I have been doing long term analysis of company expenses.  For many years, the companies reported their information  using standard department names and line numbers.  For example, LINE_NUMBER 05000 always meant MAINTENANCE.  With the last year, the

COMPANY NAME	DEPT_NAME	LINE_NUMBER	ANNUAL_EXPENSE
A	MAINTENANCE	05000	$10,000
A	TRAVEL	12000	$5,000

companies have been required to use different LINE_NUMBERS with the same department names.  So for the current year,

COMPANY NAME	DEPT_NAME	LINE_NUMBER	ANNUAL_EXPENSE
A	MAINTENANCE	03000	$15,000
A	TRAVEL	08000	$7,000

Maintenance is assigned LINE_NUMBER 03000. 

I would like to compare prior year annual expenses with the current year using LINE_NUMBERS rather than DEPT_NAMEs.  I thought I would run the prior year query and the current year query then use, what ever you suggest to convert current year LINE_NUMBERS to prior year LINE_NUMBERS.  I have a Excel crosswalk table that compares  current year LINE_NUMBERS with prior year LINE_NUMBERS.

Hope I have explained my question adequately. 

Thanks in advance for your help.

Al

Answer 1

Just join last year data on LINE_NUMBERS to Excel crosswalk table to this years data LINE_NUMBERS.

Use 2 instances of your table in query design view (Access will add a suffix of '_1' to the 2nd.

Use last year as criteria on first and this year on 2nd.

Answer 2

Now the question is if I wanted to consolidate/sum all sub scripted line numbers under their respective whole line number, would an unjoined table for the prior year and an unjoined table for the current year work?

First you should start a new thread for new questions, not piggy-back.

I don't follow your question.   Post example data and how you want results to look in a new post.

Answer 3

Hi

Your solution worked perfectly - they always do! 

Here is an additional question.  Some time ago you helped me to cluster subscripted column numbers with a table that was included in the SQL was not joined.  It looked like the table example below with many more records and worked perfectly:

column number	low	high	dept_name
000-099	000	099	Maintenance
100-199	100	199	Sales

Now the question is if I wanted to consolidate/sum all sub scripted line numbers under their respective whole line number, would an unjoined table for the prior year and an unjoined table for the current year work? I'll try it using your "build a little, test a little" and for me "learn a little" approach to see what happens.

In the mean time, thanks again for being so helpful.

Al