Getting information about record "10" while in record "9" et

[Aubs010]

Hi, I have a table that was created using a make table query:

| Ref    | Compliance |
|========|============|
| A00zz  |    N/A     |
|--------|------------|
| A04zz  |    N/A     |
|--------|------------|
| A05    |     N      |
|--------|------------|
| B00zz  |    N/A     |
|--------|------------|
| B01    |     N      |
|--------|------------|
| B02    |     Y      |
|--------|------------|
| B04    |     N      |
|--------|------------|

A00zz/A04zz/B00zz are all headings (the zz is to distinguish in reports and forms).

Now, since there are no sub headings after A00zz I would like to delete that record from the temporary table.

At a rough guess, I would think the pseudo-code for it could be:

[tt]IF NEXT Record REF ends with zz THEN do not add this record to new table[/tt]

I hope someone will understand what I have asked, and will be able to help.

If any more info is required, I will be greatful for people to ask.

Thanks in advance,


Aubs

 

[MattJW]

In the design view of your make table query add the following to the criteria (against REF field)...

Not Like "*zz"

This will filter out the Titles from your query results.

Hope this helps!

 

[Aubs010]

Hi Matt,

Thanks for your comments, what I actually wanted to do was delete *some* of the "zz" records, not all.

I posted the same question on another website and got the answer I was looking for. I'll copy it here in case future people want it:

[tt]
here is a solution that would delete the entries for you. It using two queries. The 'Delete' query uses the select query but you just execute the delete query.


This query selects each key and the next key value in sequence from the table

SELECT T1.Key as T1Key
    ,(SELECT min(T2.Key) 
        FROM tblTable T2 
       WHERE T1.KEY < T2.KEY
     ) as T2Key
FROM tblTable T1

This query will delete all key values ending in zz where the next key value also ends in zz or doesn't exist (ie last/highest key value)

DELETE tblTable.*
 FROM tblTable
WHERE tblTable.Key in 
    (SELECT T1Key 
       FROM Qry INNER JOIN tblTable 
         ON Qry.T1Key = tblTable.Key
      WHERE Right(T1Key,2)="zz" 
        AND (Right(T2Key,2)="zz" OR T2Key is null))

[/tt]


Thanks again all the same.


Aubs

 

[danvlas]

Guess you could do it in one step:

Delete * From TableName Where DCount("Ref", "TableName", "Right(' ' & [Ref], 2) = 'zz' And Left([Ref] & ' ',3) = " & Left([Ref] & " ", 3)) = 1

Make a backup before testing, as I have not tested it...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Aubs010]

hehe! Thanks for your comment Daniel, The two queries I have work now, so I've decided to leave it at that. If I start trying different things, I know I will mess something up!!

Thanks again all the same,


Aubs

 

[danvlas]

That's a wise decision, as I looked at my query again and it's crap...

I still think it can be done with just one query...but the solution you have is peachy...so go with it.

No reason to replace a working solution with a worse one






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Aubs010]

Sorry to say that I didn't even look at your code, it would have just comfused me even more!!!!!!

Thanks again for trying though ;-)


Aubs