Board index » VBA

All times are UTC



 SQL select statement 
Author Message
Sanjay Meht
#1 / 6
 SQL select statement

hi,

i have a table with customer field and company field (and
other fields). as data is entered, all records are
distinct. i want only the very first customer record as
the company changes for a particular customer.

 e.g.

customer  company   amount
smith     msft      100.00 **
smith     msft      200.00
smith     msft      300.00
smith     at&t      500.00 **
smith     at&t      200.00
smith     gte       100.00 **
smith     gte       200.00
jones     msft      100.00
jones     msft      200.00

i want to pick out the records with the stars.

thanks

sm  :)



Sat, 26 Mar 2005 18:29:51 GMT  
MV
#2 / 6
 SQL select statement
How do you determine what is the FIRST record?  Records are not ordered when
they are stored, so there must be some criteria to determine which is the FIRST
record.  Is there a date field that can be used to determine which record is
first?  Or perhaps a sequence number of some kind?
Quote:

> hi,

> i have a table with customer field and company field (and
> other fields). as data is entered, all records are
> distinct. i want only the very first customer record as
> the company changes for a particular customer.

>  e.g.

> customer  company   amount
> smith     msft      100.00 **
> smith     msft      200.00
> smith     msft      300.00
> smith     at&t      500.00 **
> smith     at&t      200.00
> smith     gte       100.00 **
> smith     gte       200.00
> jones     msft      100.00
> jones     msft      200.00

> i want to pick out the records with the stars.

> thanks

> sm  :)



Sat, 26 Mar 2005 21:07:56 GMT  
Randy at Hom
#3 / 6
 SQL select statement
If you are going to the first record, timewise, that was entered, your
database design is wrong. You require a DateTime field representing the
update time/insert time of the record, and order the tables by customer,
company, datetime. This becomes a time-varying form of a database, and
traditional joins and normalizations stop working well. If this is a large
database, I suggest you pick up a reference on time varying databases, and
start reading, because it's a big topic.


Quote:
> hi,

> i have a table with customer field and company field (and
> other fields). as data is entered, all records are
> distinct. i want only the very first customer record as
> the company changes for a particular customer.

>  e.g.

> customer  company   amount
> smith     msft      100.00 **
> smith     msft      200.00
> smith     msft      300.00
> smith     at&t      500.00 **
> smith     at&t      200.00
> smith     gte       100.00 **
> smith     gte       200.00
> jones     msft      100.00
> jones     msft      200.00

> i want to pick out the records with the stars.

> thanks

> sm  :)



Sat, 26 Mar 2005 21:57:19 GMT  
sanjay meht
#4 / 6
 SQL select statement
there is a sequence number, but for every company it does
not necessarily start with 1 ,2, 3, see eg

customer  company   amount   number
smith     msft       100       1  **
smith     msft       200       2
smith     at&t       250       8  **
smith     at&t       300       9
smith     gte        150       11 **
smith     gte        200       12
jones     msft       250       15
jones     msft       150       16
jones     at&t       150       18

i wnat the records with the ** 's

thanks

sm  :)

Quote:
>-----Original Message-----
>How do you determine what is the FIRST record?  Records

are not ordered when
Quote:
>they are stored, so there must be some criteria to

determine which is the FIRST
Quote:
>record.  Is there a date field that can be used to

determine which record is
Quote:
>first?  Or perhaps a sequence number of some kind?


>> hi,

>> i have a table with customer field and company field
(and
>> other fields). as data is entered, all records are
>> distinct. i want only the very first customer record as
>> the company changes for a particular customer.

>>  e.g.

>> customer  company   amount
>> smith     msft      100.00 **
>> smith     msft      200.00
>> smith     msft      300.00
>> smith     at&t      500.00 **
>> smith     at&t      200.00
>> smith     gte       100.00 **
>> smith     gte       200.00
>> jones     msft      100.00
>> jones     msft      200.00

>> i want to pick out the records with the stars.

>> thanks

>> sm  :)
>.



Sat, 26 Mar 2005 22:08:02 GMT  
Randy at Hom
#5 / 6
 SQL select statement
SELECT First(Table3.number) AS FirstOfnumber, Table3.customer,
Table3.company, First(Table3.amount) AS FirstOfamount
FROM Table3
GROUP BY Table3.customer, Table3.company
HAVING (((Table3.customer)="smith"))
ORDER BY First(Table3.number), Table3.customer, Table3.company;

Enjoy


Quote:
> there is a sequence number, but for every company it does
> not necessarily start with 1 ,2, 3, see eg

> customer  company   amount   number
> smith     msft       100       1  **
> smith     msft       200       2
> smith     at&t       250       8  **
> smith     at&t       300       9
> smith     gte        150       11 **
> smith     gte        200       12
> jones     msft       250       15
> jones     msft       150       16
> jones     at&t       150       18

> i wnat the records with the ** 's

> thanks

> sm  :)

> >-----Original Message-----
> >How do you determine what is the FIRST record?  Records
> are not ordered when
> >they are stored, so there must be some criteria to
> determine which is the FIRST
> >record.  Is there a date field that can be used to
> determine which record is
> >first?  Or perhaps a sequence number of some kind?


> >> hi,

> >> i have a table with customer field and company field
> (and
> >> other fields). as data is entered, all records are
> >> distinct. i want only the very first customer record as
> >> the company changes for a particular customer.

> >>  e.g.

> >> customer  company   amount
> >> smith     msft      100.00 **
> >> smith     msft      200.00
> >> smith     msft      300.00
> >> smith     at&t      500.00 **
> >> smith     at&t      200.00
> >> smith     gte       100.00 **
> >> smith     gte       200.00
> >> jones     msft      100.00
> >> jones     msft      200.00

> >> i want to pick out the records with the stars.

> >> thanks

> >> sm  :)
> >.



Sat, 26 Mar 2005 23:10:09 GMT  
MV
#6 / 6
 SQL select statement
The following SQL statement should probably work

Select Tablename.Customer, Tablename.Company, Tablename.Amount
FROM Tablename
Where Tablename.Number In
(SELECT Min(TmpNumber) as MinNum
 FROM TableName as Tmp
 Group By tmp.Customer, tmp.Company)

Quote:

> there is a sequence number, but for every company it does
> not necessarily start with 1 ,2, 3, see eg

> customer  company   amount   number
> smith     msft       100       1  **
> smith     msft       200       2
> smith     at&t       250       8  **
> smith     at&t       300       9
> smith     gte        150       11 **
> smith     gte        200       12
> jones     msft       250       15
> jones     msft       150       16
> jones     at&t       150       18

> i wnat the records with the ** 's

> thanks

> sm  :)

> >-----Original Message-----
> >How do you determine what is the FIRST record?  Records
> are not ordered when
> >they are stored, so there must be some criteria to
> determine which is the FIRST
> >record.  Is there a date field that can be used to
> determine which record is
> >first?  Or perhaps a sequence number of some kind?


> >> hi,

> >> i have a table with customer field and company field
> (and
> >> other fields). as data is entered, all records are
> >> distinct. i want only the very first customer record as
> >> the company changes for a particular customer.

> >>  e.g.

> >> customer  company   amount
> >> smith     msft      100.00 **
> >> smith     msft      200.00
> >> smith     msft      300.00
> >> smith     at&t      500.00 **
> >> smith     at&t      200.00
> >> smith     gte       100.00 **
> >> smith     gte       200.00
> >> jones     msft      100.00
> >> jones     msft      200.00

> >> i want to pick out the records with the stars.

> >> thanks

> >> sm  :)
> >.



Sun, 27 Mar 2005 04:27:00 GMT  
 
 Page 1 of 1
  [ 6 post ] 

 Relevant Pages 

1. Access: Err 2342 in SQL SELECT Statement in VBA

2. Counting records returned by SQL Select statement

3. Access: Err 2342 in SQL SELECT Statement in VBA

4. '97 Set text box value to SQL Select statement

5. SQL SELECT statement to Spreadsheet

6. Sql - select statement - Please help

7. SQL Select Statement HELP

8. WHERE clause containing a variable in a SQL SELECT statement

9. Help with SQL SELECT statement

10. Help! VB and Access SQL select statement

11. SQL - Select statement based on time, possible?

12. SQL Select statement question

  

 
Powered by phpBB® Forum Software