Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2019, 06:07 AM   #1
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
list all the number between two sets given

I have an access form where the user enters two set of numbers, for example 63 and 120. On another table I need to have all the numbers >=63 and <=120 listed.

How would I do this?

hllary is offline   Reply With Quote
Old 09-23-2019, 06:36 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,597
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: list all the number between two sets given

Hi. Welcome to AWF! Have you tried using a query? For example:
Code:
SELECT * FROM TableName WHERE FieldName >= Forms!FormName.ControlName AND FieldName <= Firms!FormName.ControlName
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-23-2019, 07:31 AM   #3
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

Thank you. Maybe I'm missing something but the output only show the the first and second numbers not all the one in between.

Below is my code:

Code:
SELECT [abc Tracker def].VarianceNumber, [abc Tracker def].[SPH Start], [abc Tracker def].[SPH End] INTO AnotherTable
FROM [abc Tracker def]
WHERE ((([abc Tracker def].[SPH Start])>=[abc Tracker def].[SPH Start]) AND (([abc Tracker def].[SPH End])<=[abc Tracker def].[SPH End]));
I need the query to show all list all the numbers between the users entered numbers. I don't not want a query that only selects a certain numbers. For example, if a user enters 52 and 60 the query will show 52, 53,,,,59,60 for that user.


Last edited by hllary; 09-23-2019 at 07:37 AM.
hllary is offline   Reply With Quote
Old 09-23-2019, 07:34 AM   #4
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,043
Thanks: 10
Thanked 207 Times in 196 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: list all the number between two sets given

I think the question is how to generate all the whole numbers between any 2 values that are provided. These missing numbers don't actually exist in any table. Correct?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 09-23-2019, 07:37 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: list all the number between two sets given

you need to reference your Form:
Code:
..
..
WHERE [abc Tracker def].[SPH Start])>=[Forms]![yourFormName]![startDateTextboxName] AND [abc Tracker def].[SPH End])<=[Forms]![yourFormName]![endDateTextboxName]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 07:40 AM   #6
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

Micron, that is correct. I'm trying to "generate all the whole numbers between any 2 values that are provided. These missing numbers don't actually exist in any table."
hllary is offline   Reply With Quote
Old 09-23-2019, 07:45 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: list all the number between two sets given

so you need to Add those numbers to the table?
or just generate them on the fly?

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 07:53 AM   #8
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

I'll have a button on a form which will run an update query and have them added to another table.
hllary is offline   Reply With Quote
Old 09-23-2019, 08:38 AM   #9
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,043
Thanks: 10
Thanked 207 Times in 196 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: list all the number between two sets given

IMHO it would be best if you stated why you want to do this as there may be better alternatives. It might even point out that the whole exercise is moot.

There are 2 basic ways I know of to generate such a list. One involves a function, which is probably the best approach. The other is to write a query/sub query. However, that approach requires that you use a table that can provide a number seed. That can be a table you create that just has numbers for this. Or the table must have an autonumber field that will always contain all the numbers you'll ever need. The potential problem with that is that if records get deleted, you have gaps in the seed values - not to mention that there's no guarantee that an AN field is always incremented by 1.

As I said, before offering examples of either method, I think it would be best to know why.
Micron is offline   Reply With Quote
Old 09-23-2019, 09:08 AM   #10
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

I have a table where the user enters a unique number/letter then they give vehicle numbers (in column b they write the smallest number. in column c they write the largest number).
The vehicle numbers go from 1 to 300. So instead of user entering all the vehicles, they write the smallest and largest number. On a separate table I will have all the vehicle's numbers that are assigned to each unique number/letter.
hllary is offline   Reply With Quote
Old 09-23-2019, 09:15 AM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: list all the number between two sets given

last question, are those numbers on the first table? or do you generate them on the fly and save it to 2nd table?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 09:22 AM   #12
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

The first and last vehicle number given on the first table, but the numbers in between can be on either the first or on a 2nd table.
hllary is offline   Reply With Quote
Old 09-23-2019, 09:30 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,892
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: list all the number between two sets given

Quote:
Originally Posted by hllary View Post
I have a table where the user enters a unique number/letter then they give vehicle numbers (in column b they write the smallest number. in column c they write the largest number).
The vehicle numbers go from 1 to 300. So instead of user entering all the vehicles, they write the smallest and largest number. On a separate table I will have all the vehicle's numbers that are assigned to each unique number/letter.
Perhaps I'm being dense but why can't the vehicle numbers be assigned to each unique number/letter in the main table? I don't see the need for the second table.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-23-2019, 09:32 AM   #14
hllary
Newly Registered User
 
Join Date: Sep 2019
Posts: 29
Thanks: 20
Thanked 0 Times in 0 Posts
hllary is on a distinguished road
Re: list all the number between two sets given

each unique number has more than one vehicle assigned to it.
hllary is offline   Reply With Quote
Old 09-23-2019, 09:37 AM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: list all the number between two sets given

ok, based from your last post, the numbers are already in either table?
here goes:

create a query first, name it qryNumbers:
Code:
SELECT T1.NUMS+1+(T2.NUMS*10)+(T3.NUMS*100) AS NewNum FROM
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T1,
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T2,
(SELECT DISTINCT Abs([ID] Mod 10) AS NUMS
FROM MSYSOBJECTS) As T3);
create another query (final query) on [abc Tracker def] table and qryNumbers:
Code:
SELECT [abc Tracker def].VarianceNumber, qryNumbers.NewNum 
    FROM [abc Tracker def], qryNumbers 
    WHERE qryNumbers.NewNum BETWEEN [abc Tracker def].[SPH start] 
             AND [abc Tracker def].[SHP end];

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
form access , update querry

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combi1 list sets criteria for combi2 ECEK Forms 2 01-22-2015 02:33 PM
List box number format wjyoung Forms 3 09-12-2011 02:14 PM
Number list XXD Forms 9 03-11-2009 08:23 AM
List of record number eugz Modules & VBA 2 12-26-2006 03:33 PM
Number Formats in List Box rich.barry Forms 2 04-20-2003 03:12 PM




All times are GMT -8. The time now is 04:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World