[RESOLVED] SELECT x records from table using the Between command

[chilling]

Is it possible to create a SELECT command to show the Header_ID for each SPLIT record when the file structure is as follows:

Table HEADER
Header_ID (which is equal to the first Split_ID in the SPLIT table)
Split_Count
Amount_Total (total of the transaction's Amount_Split records in the SPLIT table)

Table SPLIT
Split_ID
Amount_Split

ie there is no field in the SPLIT table holding the HEADER.Header_ID.

A transaction consists of a HEADER record and a number of SPLIT records.
The SPLIT table Split_ID is an auto number and generatd sequentally.
The HEADER table hold the first Split_ID and the number of SPLIT records which make up the transaction.

ie If the next Split_ID ie 345 and the next transaction had three Split records and the next but one transaction had two Split reords the following records would be added to the two tables:

Table SPLIT
Split_ID
345
346
347

348
349


Table HEADER
Header_ID = 345, Split_Count = 3
Header_ID = 348, Split_Count = 2

[brucevde]

Try

Select *
From Header 
     Inner Join Split On Split_Id Between Header_Id And (Header_Id + Split_Count) - 1

Syntax may depend on which database you are using...

[chilling]

This is an Access database with the tables linked to an other database.

The actual name of the tables are ... Audit_Header and Audit_Split

The relevent fields are ....
- Audit_Header.Tran_Number
- Audit_Header.Item_Count
and
- Audit_Split.Tran_Number

the script I tried is:

SELECT *
  FROM Audit_Header 
       INNER JOIN Audit_Split ON Audit_Split.Tran_Number
           BETWEEN Audit_Header.Tran_Number 
           AND (Audit_Header.Tran_Number + Audit_Header.Item_Count) - 1

unfortunately it does not work.

[sevenhalo]

You lost me... Please explain these two statements to me:

Header_ID (which is equal to the first Split_ID in the SPLIT table)

there is no field in the SPLIT table holding the HEADER.Header_ID

It sounds like you're trying to have a one to one relationship. Am I correct?

[sevenhalo]

OMG, I know what you're doing now... wow...

Ok, first of; are you going to be able to change the data model or are you stuck with this design?

[chilling]

What I want is a one to many relationship between the Audit_Header to Audit_Split table.

The problem is that there is no field in the Audit_Split with the index field in the Audit_Header. What there is, is a field in the Audit_Header table telling me which is the first record in the Audit_Spilt table and how many of the following records also relate to this Audit_Header record.

[chilling]

Ok, first of; are you going to be able to change the data model or are you stuck with this design?

Stuck with the design

[sevenhalo]

It's a one to one essentially, you're just using the data in the one to one to widen the scope as if it was many. You really shouldn't do it this way. I'd be more then willing to help you figure out some keys for the table if you're able to change them?

Otherwise, I can try and throw something together for this to work. I'm warning you though, it's not going to be very pretty or efficient because you're going to have to subquery and have where clauses against fields that probably aren't indexed. Since you're using the value of "Audit_Header.Item_Count" to determine your keys, that all needs to be found first.

[chilling]

I am comletely stuck with database design - I can not even get into the database to add any keys etc ... I can only link to the database - read only.

[sevenhalo]

It'll make it really hard down the road when you need to add functionality. I guess you can cross that bridge when you get there.

This should work for you (mind you, I don't have your data model nd couldn't test it. You may need to make slight modifications):

SELECT * FROM Audit_Split WHERE Audit_Split.Tran_Number in 
	(SELECT Audit_Header.Tran_Number FROM Audit_Header WHERE Audit_Header.TranNumber 
	between Audit_Header.Tran_Number-1 AND Audit_Header.Tran_Number + Audit_Header.Item_Count-1

Edit: I changed it to use AND

[brucevde]

This is the syntax for Access, you were just missing some braces.

SELECT *
  FROM Audit_Header 
       INNER JOIN Audit_Split ON (Audit_Split.Tran_Number
           BETWEEN Audit_Header.Tran_Number 
           AND (Audit_Header.Tran_Number + Audit_Header.Item_Count) - 1)

[Edit]

Scrap that, your syntax worked for me as well.

Can you tell us what you meant by "unfortunately it does not work"? Syntax error, incorrect data being returned or something else.

[chilling]

I am confused .... there is a "between" and no "and"

[chilling]

Hi brucevde - thanks for the input but that is giving me the following error:

ODBC--call failed.

Syntax error:Invalid filter in WHERE clause (#0)

[brucevde]

What are you using to execute the query? From VB using ADO, DAO???

[sevenhalo]

I used a comma. It may or may not work depending on your db and connection type. Give it a shot, can't hurt. it's only a select

[chilling]

brucevde: Currently I am doing all this purely in Access, I am trying to get the SELECT statement sorted out before I put into the VB code ... it will be VB.NET ODBC

sevenhalo: Where should the comma go?

[sevenhalo]

The commas already there, just copy it nerd

[brucevde]

I used Access as well. Here is my test database. Tell me what is different with yours.

[chilling]

OK I have found the problem ... if I copy the tables from the external tables into access and create tables in access then the query works exactly as you say ... but if I run the query against LINKED tables in the access database the query fails ....?

[chilling]

Thanks for all the help guys ... I will get around the limitations of the external databases ODBC driver by creating temp tables to enable me to link the two underlying tabes.