list all the number between two sets given

[hllary]

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?

 

[theDBguy]

Hi. Welcome to AWF! Have you tried using a query? For example:

SELECT * FROM TableName WHERE FieldName >= Forms!FormName.ControlName AND FieldName <= Firms!FormName.ControlName

 

[hllary]

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:

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.

 

[Deleted member 147267]

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?

 

[arnelgp]

you need to reference your Form:

..
..
WHERE [abc Tracker def].[SPH Start])>=[Forms]![yourFormName]![startDateTextboxName] AND [abc Tracker def].[SPH End])<=[Forms]![yourFormName]![endDateTextboxName]

 

[hllary]

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."

 

[arnelgp]

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

 

[hllary]

I'll have a button on a form which will run an update query and have them added to another table.

 

[Deleted member 147267]

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.

 

[hllary]

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.

 

[arnelgp]

last question, are those numbers on the first table? or do you generate them on the fly and save it to 2nd table?

 

[hllary]

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.

 

[isladogs]

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.

 

[hllary]

each unique number has more than one vehicle assigned to it.

 

[arnelgp]

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

create a query first, name it qryNumbers:

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:

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];

 

[isladogs]

each unique number has more than one vehicle assigned to it.

So? Why does that matter? You would assign the letter to the vehicle not the other way round

 

[Pat Hartman]

Some of us are still confused, mostly because it doesn't generally make sense to just add a bunch of rows to a table with only a sequence number filled in.

We still don't understand the purpose, Rows should only be added to a table when they contain some data that needs to be saved in a separate row. So far, your description doesn't rise to that standard. What other data goes into these rows and when is it entered? and why aren't the rows just entered when the other data is available?

There are reasons for doing stuff like this. For example, when a teacher gives a test, he might generate a record for each student who needs to take the test with null as the actual grade to indicate that the test has not yet been graded. The individual records serve as holding spots for the grades since EVERY student will eventually have a grade and rather than have an audit turn up a "missing" row at some future time, ALL rows are added now and the grade is filled in later. If the rows are not added "now", it will be difficult "later" to determine if a particular student's record is just not scored or if he was never supposed to take the test.

 

[Deleted member 147267]

Agree with the confusion thing. I was thinking "So if I have to add 10 vehicles I'm supposed to know that the last one entered was 100 thus I enter 101 as a start value and 110 as the end value?" What if I miscalculate? What if 2 or more concurrent users are entering a block of numbers? The method doesn't make sense; at least not as it's being interpreted. There are other ways of entering group data. Which is best depends on who's doing what and maybe at the same time.

 

[jdraw]

hllary,

By now you must recognize your post is confusing readers. Can you step back from HOW you think you should solve something and tell readers in simple English what you are trying to achieve. There may be options, but before we can give focused advice, we have to understand the basic problem.
You have numbers and forms and at least 2 tables in your proposed approach. But you mention vehicles, so what is the business issue to be resolved?