Query from different table in one column

[ice051505]

Hi all, I got a urgent situation here, Please help me out, Thanks!

I have two tables,
Table A:
Year Type Count
1992 Walk 82
1992 Bike 43
1992 Vechicle 298
1992 Ship 3
1992 flight 3478
1993 Walk 47
1993 Bike 13
1993 Vechicle 638
1993 Ship 24
1993 flight 3111
1994 Walk 0
1994 Bike 8
1994 Vechicle 1298
1994 Ship 24
1994 flight 3234

Table B:
ID Type
T1 Walk
T2 Bike
T3 Vechicle
T4 Ship
T5 flight
The two table linked by the [type]
I need a query expression like: SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2',[TableA].[Count])
FastWay: IIf([TableB].[ID]= 'T3' AND [TableB].[ID]= 'T4' AND [TableB].[ID]= 'T5',[TableA].[Count])

My expression keep gives me the error, can anyone fix for me? Many Many Thanks!!!​

 

[plog]

No, it can't be fixed. You have mutually exclusive criteria in your IIF statement:

SlowWay: IIf([TableB].[ID]= 'T1' AND [TableB].[ID]= 'T2'...

[TableB].[ID] can only have one value at a time, you're asking to make sure it has 2 values at the same time. The above condition will always fail.

Without code, can you try to explain what you want? Better yet, based on the sample data you have already posted, what would you like the result of the query to produce?

 

[ice051505]

Thank you for reply!!!

The result I want is:
Year | SlowWay | FastWay
1992---125--------3779
1993---60---------3773
1994---8----------4556

For example: the number 125 in 1992 coming from the total count of walk and bike,
the number 3779 in 1992 coming from the total number of vechicle, ship and flight.
Can you help me to achieve that? Many Many Thanks!

Without code, can you try to explain what you want? Better yet, based on the sample data you have already posted, what would you like the result of the query to produce?

 

[plog]

I'm sorry, I'm not going to show you how to do that--your database is improperly structured.

1. Year, Type and Count are reserved words in Access and shouldn't be used as object names. I would rename your fields by prefixing them with something descriptive of the data (i.e. TravelCount, TravelYear, TravelType).

2. You have an ID field in Table B, but your not using it. Table A should have the ID from Table B, not the Type. Additionally that ID in Table B should be numeric. Do all the ID numbers in Table B have a 'T' in front of them? If so, you don't need that T.

3. Table B should have an additional column that designates whether the Type is Slow or Fast (or any other designation). This will make creating your query easier.

 

[ice051505]

Thank you, I will try your advice, Thanks a lot!!!