Between Query

[Boomshank]

Hi,


I am trying to calculate the total number of mail pieces between two records. The number of records may vary between the two ID's.

I am trying to create a query that when run will ask me to type in a starting ID number then ask me to enter an End ID number then want it to show me the two numbers i entered along with the total mailpieces between the two of them.

Can anyone help?

Thanks

 

[DCrake]

SELECT First(ID) AS FirstOfID, Last(ID) AS LastOfID, Sum(YourField) AS Total
FROM YourTableName
WHERE (((ID) Between [Start] And [End]));

 

[MSAccessRookie]

Hi,


I am trying to calculate the total number of mail pieces between two records. The number of records may vary between the two ID's.

I am trying to create a query that when run will ask me to type in a starting ID number then ask me to enter an End ID number then want it to show me the two numbers i entered along with the total mailpieces between the two of them.

Can anyone help?

Thanks

I think you needf to Count() the items in between. Something like:

SELECT First(IDNumber) AS FirstID, Last(IDNumber) AS LastID, Count(*) AS TotalIDNumbers
FROM YourTableName
WHERE IDNumber Between [Enter Starting ID] And [Enter Ending ID];

This will give you a Count of the number of items between the Start and End including the Start and End. If you only want the number of items IN BETWEEN them, you will need to subtract 2.