How to get Table3 's result by query?

[amolin]

Hi, all,

I have a big difficulties; How can get the table 3 's result from table1 and table 2 by query?
Please see attachment.

table1
Name-------Age
a-------12
b-------25
c-------33
d-------38

Table2

Age Limit-------Ticket Price
10-------------15
20-------------25
30-------------35
40-------------45
50--------------55

table3
Name------Age------Age Limit------Ticket Price
a------12------10 ------15
b------25------20 ------25
c------33------30 ------35
d------38------30------35

Thank you.

 

[Parker]

Hi, all,

I have a big difficulties; How can get the table 3 's result from table1 and table 2 by query?
Please see attachment.

Thank you.

Like this?

 

[Jon K]

Since the range in each age bracket is 10 years, you can join the tables with:-

WHERE Table1.Age BETWEEN Table2.[Age Limit] AND Table2.[Age Limit]+9

in a query. See the query in the database.


If the ranges in the age brackets are of different lengths, you can add a field [Age Limit End] in Table2 and join the tables with:-

WHERE Table1.Age BETWEEN Table2.[Age Limit] AND Table2.[Age Limit End]

 

[amolin]

Since the range in each age bracket is 10 years, you can join the tables with:-

WHERE Table1.Age BETWEEN Table2.[Age Limit] AND Table2.[Age Limit]+9

in a query. See the query in the database.


If the ranges in the age brackets are of different lengths, you can add a field [Age Limit End] in Table2 and join the tables with:-

WHERE Table1.Age BETWEEN Table2.[Age Limit] AND Table2.[Age Limit End]

Thank you for your idea!

but the age bracket is not always 10 years, so
could you have any idea to work out this? By the way, the table1 and table2 is not of my control,so I could not revise Table1 and and Table2's structure.

Thank you again!

 

[Jon K]

Then you will need a series of two queries. The first query creates the [Age Limit End] field based on the [Age Limit] field. The second query joins Table1 with the first query using:-

WHERE Table1.Age BETWEEN qryOne.[Age Limit] AND qryOne.[Age Limit End]


You can run the second query in the database.