Two table query

[Greg Jesky]

I may be asking more than access can deliver.
I have 2 tables:
In the first table I have a mobile telephone number with the average number
of minutes used the previous three months together with additional fields
In the second table I have a record for each several different rate plans
provided by wireless providers(Cingular,Verizon T-Mobile,etc)
I would like to pull each record from the first table and select in the
second table the record with cell plan minutes greater than the minutes used
in the first table.
For example
Table 1
Unit # Average Min Used Service Provider
770 333 3333 637 Cingular

Table 2
Cell Plan Minutes included in Plan Service Provider
001 400 Cingular
002 600 Cingular
003 800 Cingular

I would like to select from table 2 the record for cell plan 003 with 800
minutes because 637 is greater than 600 but less than 800. Can this be done
with my access 2000 and how would you do it?
Thank You,
Greg

 

[David Seeto via AccessMonster.com]

This would be challening not just for Access but for any SQL based
database, because joining two tables requires the fields to match exactly -
in this case, you don't want an exact match between Average Minutes Used
and Minutes Included, but want to match with something that doesn't match
exactly.

In the example you've given, you could work around it by creating a query
that rounds the Average Minutes Used up to the nearest 200, and then match
that exactly with the Cell Plan table - unfortunately, this approach gets
messy if the pattern isn't consistent (eg. 100 min, 200 min and 400 minutes
plans) and you would need to cater for the maximum number of minutes in any
plan. Furthermore, different carriers could have different plans, so you'd
almost need a different calculated field for each of them - as I said,
messy.

It's this sort of enquiry that is often better handled in actual code (e.
VBA) than SQL queries...

 

[Van T. Dinh]

***Untested SQL***
SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.[MinsIncluded] In
( SELECT Min(Sub.[MinsIncluded])
FROM Table2 AS Sub
WHERE Sub.[MinsIncluded] > Table1.[MinsIncluded] )

There may be more efficient SQLs but I can't think of them right now.

HTH
Van T. Dinh
MVP (Access)

 

[Greg Jesky]

David and Van,
Thank you both for your comments.
I am trying Van's suggestion and will also be looking at reading the
necessary tables directly. However this may take me some time to learn. If I
was to try to write VB(A) code to read the tables directly with output from
the code going into a new different table, do you have any suggestions as to
where I can find some information and examples of how to do this? (DAO or
ADO ?) I am running W2000(latest service pack) and Access 2000 on a stand
alone system(hardware). No client/server or peer to peer.
Thank You,
Greg

***Untested SQL***
SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.[MinsIncluded] In
( SELECT Min(Sub.[MinsIncluded])
FROM Table2 AS Sub
WHERE Sub.[MinsIncluded] > Table1.[MinsIncluded] )

There may be more efficient SQLs but I can't think of them right now.

HTH
Van T. Dinh
MVP (Access)

I may be asking more than access can deliver.
I have 2 tables:
In the first table I have a mobile telephone number with the average number
of minutes used the previous three months together with additional fields
In the second table I have a record for each several different rate plans
provided by wireless providers(Cingular,Verizon T-Mobile,etc)
I would like to pull each record from the first table and select in the
second table the record with cell plan minutes greater than the minutes used
in the first table.
For example
Table 1
Unit # Average Min Used Service Provider
770 333 3333 637 Cingular

Table 2
Cell Plan Minutes included in Plan Service Provider
001 400 Cingular
002 600 Cingular
003 800 Cingular

I would like to select from table 2 the record for cell plan 003 with 800
minutes because 637 is greater than 600 but less than 800. Can this be done
with my access 2000 and how would you do it?
Thank You,
Greg

 

[Greg Jesky]

Van,
Thank you for your suggestion. Can you explain the where statement? I can't
seem to get this sql to work and I know it is probably my fault.
Thank You,
Greg

***Untested SQL***
SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.[MinsIncluded] In
( SELECT Min(Sub.[MinsIncluded])
FROM Table2 AS Sub
WHERE Sub.[MinsIncluded] > Table1.[MinsIncluded] )

There may be more efficient SQLs but I can't think of them right now.

HTH
Van T. Dinh
MVP (Access)

I may be asking more than access can deliver.
I have 2 tables:
In the first table I have a mobile telephone number with the average number
of minutes used the previous three months together with additional fields
In the second table I have a record for each several different rate plans
provided by wireless providers(Cingular,Verizon T-Mobile,etc)
I would like to pull each record from the first table and select in the
second table the record with cell plan minutes greater than the minutes used
in the first table.
For example
Table 1
Unit # Average Min Used Service Provider
770 333 3333 637 Cingular

Table 2
Cell Plan Minutes included in Plan Service Provider
001 400 Cingular
002 600 Cingular
003 800 Cingular

I would like to select from table 2 the record for cell plan 003 with 800
minutes because 637 is greater than 600 but less than 800. Can this be done
with my access 2000 and how would you do it?
Thank You,
Greg

 

[Van T. Dinh]

For each Record/row in Table1, the SubQuery selects the minimum value of
[MinsIncluded] in Table2 that is greater than the [MinsIncluded] value of
the "current" Record in Table1.

Thus, the main SELECT clause will select Records/rows of Table1 and for each
Record/row, select the "corresponding" Record/row in Table2, i.e. the
Record/row in Table2 that has the minimum [MinsIncluded] which is greater
than the Table1.[MinsIncluded].

For more details, see The Access Web:

http://www.mvps.org/access/queries/qry0020.htm

HTH
Van T. Dinh
MVP (Access)

 

[Greg Jesky]

Van,
Thank you for your help I think I can make this work I appreciate your
patience.
Greg

For each Record/row in Table1, the SubQuery selects the minimum value of
[MinsIncluded] in Table2 that is greater than the [MinsIncluded] value of
the "current" Record in Table1.

Thus, the main SELECT clause will select Records/rows of Table1 and for each
Record/row, select the "corresponding" Record/row in Table2, i.e. the
Record/row in Table2 that has the minimum [MinsIncluded] which is greater
than the Table1.[MinsIncluded].

For more details, see The Access Web:

http://www.mvps.org/access/queries/qry0020.htm

HTH
Van T. Dinh
MVP (Access)

Van,
Thank you for your suggestion. Can you explain the where statement? I can't
seem to get this sql to work and I know it is probably my fault.
Thank You,
Greg

 

[Greg Jesky]

Van,

I was wrong, I am still having problems!! Your description of what your
suggested SQL does (listed below) is dead on correct.
However, I do not have a relationship between the 2 tables because a
relationship does not make sense to me.
Does this make a difference to your suggested SQL?
One table contains records of average usage by cellular telephone number.
The second table contains records of different cellular plans sold by
companies like Verizon or T-mobile or Cingular.
I believe you understand what I am trying to do..

I keep trying to run your suggested SQL and receive request for parameters
(3 of them) and then get blank results. I am very new at SQL.

I hope you are still willing to help me. Can I send my SQL to you via this
forum and will you look at it??

Thanks,
Greg

Van,
Thank you for your help I think I can make this work I appreciate your
patience.
Greg

For each Record/row in Table1, the SubQuery selects the minimum value of
[MinsIncluded] in Table2 that is greater than the [MinsIncluded] value of
the "current" Record in Table1.

Thus, the main SELECT clause will select Records/rows of Table1 and for each
Record/row, select the "corresponding" Record/row in Table2, i.e. the
Record/row in Table2 that has the minimum [MinsIncluded] which is greater
than the Table1.[MinsIncluded].

For more details, see The Access Web:

http://www.mvps.org/access/queries/qry0020.htm

HTH
Van T. Dinh
MVP (Access)

Van,
Thank you for your suggestion. Can you explain the where statement? I can't
seem to get this sql to work and I know it is probably my fault.
Thank You,
Greg

 

[Van T. Dinh]

I didn't use any relationship in the SQL String so Table relationships don't
make any difference to the SQL.

See the article I referred to previously.

If you still can't get it to work, post details of your Tables and your
attempted SQL.

HTH
Van T. Dinh
MVP (Access)

 

[Greg Jesky]

Van,

I did review the link you gave me and it did help with my understanding but
I am apparently missing some SQL basics.
Directly below is my SQL.


SELECT [TBL-customer wireless usage].*, [TBL-Wireless carrier plan
offerings].*
FROM [TBL-customer wireless usage], [TBL-Wireless carrier plan offerings]
WHERE [TBL-Wireless carrier plan offerings].[Plan peak minutes provided] IN
(SELECT MIN ([Sub].[Plan Peak Minutes provided]) FROM [TBL-Wireless
carrier plan offerings] AS [Sub] Where [Sub].[Plan peak minutes
provided]>[ TBL-customer wireless usage].[AVGOFPeak Usage Min] );

Table name: TBL-Customer Wireless Usage:

Unit No, text (usually a telephone number)
SumOfPeak Usage Min, number
SumOfOff Peak Usage Min, number
AvgOfPeak Usage Min, number
AvgOfOff Peak Usage Min, number

Table Name: TBL-Wireless Carrier Plan Offerings

Cell Plan Id , text
Cell Plan Name, text
Service Provider, text
Plan Peak Min Provided, number
Plan Off Peak Min Provided

Van, thank you for your patience with a novice. Maybe I will be able to help
someone else some day.

Greg

 

[Van T. Dinh]

Does your SQL run?

Any error message?

Something not right right with the return of the Quey / SQL?

If possible, post a small set of sample data, what the Query / SQL returns
and what you _want_ the Query to return.

 

[Greg Jesky]

Van,

I was initially receiving a series of prompts for Parameter input. This was
my problem because I did not type field and table names in my sql exactly as
my tables. I have fixed this problem...

THE SQL NOW RUNS EXACTLY AS INTENDED!!

I have spent about 4 -6 hours getting this to work and with your assistance
have learned a tremendous amount about SQL and ACCESS 2000.

I still do not understand the use of the alias in your SQL but I am still
searching "help".

Thank You for your assistance!

Greg

PS The link you gave me is much appreciated

Does your SQL run?

Any error message?

Something not right right with the return of the Quey / SQL?

If possible, post a small set of sample data, what the Query / SQL returns
and what you _want_ the Query to return.

--
HTH
Van T. Dinh
MVP (Access)

Van,

I did review the link you gave me and it did help with my understanding but
I am apparently missing some SQL basics.
Directly below is my SQL.


SELECT [TBL-customer wireless usage].*, [TBL-Wireless carrier plan
offerings].*
FROM [TBL-customer wireless usage], [TBL-Wireless carrier plan offerings]
WHERE [TBL-Wireless carrier plan offerings].[Plan peak minutes provided] IN
(SELECT MIN ([Sub].[Plan Peak Minutes provided]) FROM [TBL-Wireless
carrier plan offerings] AS [Sub] Where [Sub].[Plan peak minutes
provided]>[ TBL-customer wireless usage].[AVGOFPeak Usage Min] );

Table name: TBL-Customer Wireless Usage:

Unit No, text (usually a telephone number)
SumOfPeak Usage Min, number
SumOfOff Peak Usage Min, number
AvgOfPeak Usage Min, number
AvgOfOff Peak Usage Min, number

Table Name: TBL-Wireless Carrier Plan Offerings

Cell Plan Id , text
Cell Plan Name, text
Service Provider, text
Plan Peak Min Provided, number
Plan Off Peak Min Provided

Van, thank you for your patience with a novice. Maybe I will be able to help
someone else some day.

Greg

 

[Van T. Dinh]

Aliases can be use for number of different things:

* When you have a calculated value, JET needs a Field name for the
calculated value (default is "Expr1" in the Access QBE). In the QBE, you
often see something like:

FullName: [LastName] & ", " & [FirstName]

(in SQL:
..., [LastName] & ", " & [FirstName] AS FullName, ...
)
In this case, the FullName is a calculated value derived from the values of
the 2 Fields [LastName] & [FirstName].

* When you want to distinguish 2 or more copies of the SAME Table in your
SQL.

You can see in the SQL String I posted previously there are 2 copies of
Table 2 in the whole SQL (1 in the SubQuery and another one in the Main
Query). I use the alias [Sub] to refer to the copy of Table2 in the
SubQuery.

* Sometimes, I use aliases simply to shorten the SQL or to simplify the SQL
String, especially when I need "similar" Queries / SQL String. For example,
you may have an SQL like:

SELECT [TableWithLongTableName].[Field1],
[TableWithLongTableName].[Field2],
[TableWithLongTableName].[Field3],
[TableWithLongTableName].[Field4],
{you get the idea ...}
FROM [TableWithLongTableName]

instead of the above, I can use alias to shorten it like:

SELECT T1.[Field1],
T1.[Field2],
T1.[Field3],
T1.[Field4],
....
FROM [TableWithLongTableName] AS T1

If I need to use the same SQL String with a different TableName, I need to
replace only 1 instance of the name in the 2nd SQL rather than numerous
instances with the previous SQL.

As you have noticed, the QBE cannot handle some types of Query and you need
to use the SQL View to type the SQL String. This way, you can shorten the
SQL String and reduce typing errors, especially with long name.

Note that I use T1 above in the example because I am lazy. However, you
should use abbreviations that make sense to you, e.g. I used [Sub] to
indicate the copy in the SubQuery.

 

[Greg Jesky]

Van,
Thank You,
Greg

Aliases can be use for number of different things:

* When you have a calculated value, JET needs a Field name for the
calculated value (default is "Expr1" in the Access QBE). In the QBE, you
often see something like:

FullName: [LastName] & ", " & [FirstName]

(in SQL:
..., [LastName] & ", " & [FirstName] AS FullName, ...
)
In this case, the FullName is a calculated value derived from the values of
the 2 Fields [LastName] & [FirstName].

* When you want to distinguish 2 or more copies of the SAME Table in your
SQL.

You can see in the SQL String I posted previously there are 2 copies of
Table 2 in the whole SQL (1 in the SubQuery and another one in the Main
Query). I use the alias [Sub] to refer to the copy of Table2 in the
SubQuery.

* Sometimes, I use aliases simply to shorten the SQL or to simplify the SQL
String, especially when I need "similar" Queries / SQL String. For example,
you may have an SQL like:

SELECT [TableWithLongTableName].[Field1],
[TableWithLongTableName].[Field2],
[TableWithLongTableName].[Field3],
[TableWithLongTableName].[Field4],
{you get the idea ...}
FROM [TableWithLongTableName]

instead of the above, I can use alias to shorten it like:

SELECT T1.[Field1],
T1.[Field2],
T1.[Field3],
T1.[Field4],
....
FROM [TableWithLongTableName] AS T1

If I need to use the same SQL String with a different TableName, I need to
replace only 1 instance of the name in the 2nd SQL rather than numerous
instances with the previous SQL.

As you have noticed, the QBE cannot handle some types of Query and you need
to use the SQL View to type the SQL String. This way, you can shorten the
SQL String and reduce typing errors, especially with long name.

Note that I use T1 above in the example because I am lazy. However, you
should use abbreviations that make sense to you, e.g. I used [Sub] to
indicate the copy in the SubQuery.

--
HTH
Van T. Dinh
MVP (Access)

Van,

I was initially receiving a series of prompts for Parameter input. This was
my problem because I did not type field and table names in my sql

exactly

as
my tables. I have fixed this problem...

THE SQL NOW RUNS EXACTLY AS INTENDED!!

I have spent about 4 -6 hours getting this to work and with your assistance
have learned a tremendous amount about SQL and ACCESS 2000.

I still do not understand the use of the alias in your SQL but I am still
searching "help".

Thank You for your assistance!

Greg

PS The link you gave me is much appreciated