Eliminate records of one table based on another table record count 
Author Message
 Eliminate records of one table based on another table record count

Hi,

How would I go about in completing the following as I can't seem to make it
happen through queries...

I have 2 tables where the first table "Table1" holds the proper record
count, and a second table "Table2" where at times may hold too many records.
Based on "Table1" record count, I basically need to eliminate (or mark) any
excess records in "Table2 going from least to greater.

Table 1

Ref # 1 (Numeric Only)

1111111
1111111
1111111
2222222
2222222
___________________
Table 2

Ref # 1            Ref # 2 (Alpha Numeric)

1111111        W00001  (Based on "Table1" count, this record should be
removed)
1111111        W00002
1111111        W00003
1111111        W00004
2222222        W00005  (Based on "Table1" count, this record should be
removed)
2222222        W00006
2222222        W00007
___________________

Result should be:

1111111        W00002
1111111        W00003
1111111        W00004
2222222        W00006
2222222        W00007

Thanks again,

Norm



Thu, 08 Sep 2005 22:46:58 GMT  
 Eliminate records of one table based on another table record count
Well, based on what I see (or understand...) you are not 'fully' exploiting the concepts of 'relational' database design...... (and
might your 'normalization' of it not be 100%).

It would look like this: Table 1 contains unique values with a related record in table 2. Once you delete a record in table 1, it
could be deleted in table 2 via 'cascading delete' (although that is a bit tricky, so won't recommend it).

In your case I:
1) Can't see why you have 2 tables in first place: if there is a one-on-one relationship, just put them in one table
2) I learn from the example that based on the number of records in T1 (3 * 1111111) you delete the 'oldest\lowest' record in T2:
W00001. But there is no relationship other then the number of records?

Based on this I suggest you look into your database design: try to put it in one table. If that's no option, put in T1 only one
record (1111111), in T2 'subrecords': W00001 and up. Then you only have to delete records from T2.

Jacques

Hi,

How would I go about in completing the following as I can't seem to make it
happen through queries...

I have 2 tables where the first table "Table1" holds the proper record
count, and a second table "Table2" where at times may hold too many records.
Based on "Table1" record count, I basically need to eliminate (or mark) any
excess records in "Table2 going from least to greater.

Table 1

Ref # 1 (Numeric Only)

1111111
1111111
1111111
2222222
2222222
___________________
Table 2

Ref # 1            Ref # 2 (Alpha Numeric)

1111111        W00001  (Based on "Table1" count, this record should be
removed)
1111111        W00002
1111111        W00003
1111111        W00004
2222222        W00005  (Based on "Table1" count, this record should be
removed)
2222222        W00006
2222222        W00007
___________________

Result should be:

1111111        W00002
1111111        W00003
1111111        W00004
2222222        W00006
2222222        W00007

Thanks again,

Norm



Fri, 09 Sep 2005 01:31:09 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Deleting records from one table based on data from another

2. How to retrieve records from one table based on other

3. need something like foreach record in table - add record to another table

4. Copy fields from record in table A to record in table B using VBA

5. records transfer from one table to another table

6. Cannot add record to Visual Foxpro table after deleting all records in table

7. Auto create record for one-to-one relationship tables

8. need help please:joined tables,add new entries based on one table columns

9. need help please:joined tables,add new entries based on one table columns

10. How to count the records added to a table

11. Record Count on a closed table

12. Dynamic Counter in a Table or a Query to Count Records (Q199679)

 

 
Powered by phpBB® Forum Software