Design Query to select Child Records with no parent record from 1field

[theCityLight]

I have an Access Database of about 70,000 asset records.The records
are a combination of parent and sub assets in a field called
AssetTrim. Each asset record is not labeled as parent or subasset but
one can identify the subassets based on the length of the string of
characters. For Example,
Table Name is 01EN. Field Name is AssetTrim.
AssetTrim
A9211
A921101
A921102
A921103
A921104
A921105
A921106
V6317
KK7335
G533501
Z8432
DA863101
DA869602
DA869603
DA869604
XA3452
XA345201
XA345202
BD175634
BD17563401
BD63984109
In the Table above,
A9211 is a parent asset with 6 subassets(A921101, A921102, A921103,
A921104, A921105, A921106). XA3452 and BD175634 are also parent assets
with sub assets.

V6317, KK7335 and Z8432 are parent assets with no subasset.

G533501, DA863101 through DA863104 and BD63984109 are subassets with
no parent assets.

I am trying to design a query that will select from the AssetTrim
field all subassets without a parent. So applying it to the table
above, the query's output will be:

G533501
DA863101
DA869602
DA869603
DA869604
BD63984109

 

[Clifford Bass]

Hi,

Basic question: How do you know that those are subassets and not parents?

Clifford Bass

 

[theCityLight]

Hi,

     Basic question: How do you know that those are subassets and not parents?

             Clifford Bass









- Show quoted text -

A parent record has this structure:
1 or 2 alpha characters, followed by 4 or 6 numeric characters

A subasset record has this structure:
1 or 2 alpha characters, followed by 4 or 6 numeric characters
followed by 2 numeric characters

 

[Clifford Bass]

Hi,

That translates to:

A parent record has this structure:
1 or 2 alpha characters, followed by 4 or 6 numeric characters

A subasset record has this structure:
1 or 2 alpha characters, followed by 6 or 8 numeric characters

Which leaves an overlapping of values. A few of examples:

These are clearly parents:

A1234
AB1234

And these are clearly subassets:

A12345678
AB12345678

But, assuming there there is no A1234 / AB1234 or A12345678 / AB12345678,
which are these (parents without subassets or subassets without parents)?

A123456
AB123456

Clifford Bass

 

[theCityLight]

Hi,

     That translates to:

A parent record has this structure:
1 or 2 alpha characters, followed by 4 or 6 numeric characters

A subasset record has this structure:
1 or 2 alpha characters, followed by 6 or 8 numeric characters

     Which leaves an overlapping of values.  A few of examples:

These are clearly parents:

A1234
AB1234

And these are clearly subassets:

A12345678
AB12345678

But, assuming there there is no A1234 / AB1234 or A12345678 / AB12345678,
which are these (parents without subassets or subassets without parents)?

A123456
AB123456

             Clifford Bass






- Show quoted text -

Apologies for my lack of clarity..

Answering your question, A1234(56) and AB1234(56) will be subassets
according to the design of the records in my table.

In my table only assets starting with BD have their parents assets
with the structure: 2 alpha characters and 6 numeric characters and
their sub assets with structure: 2 alpha characters and 8 numeric
characters (the 2 extra characters at the end identifies them as
subassets)

For all other assets,
the structure is
Parent record - 1 or 2 alpha characters, followed by 4 numeric
characters

Subasset record - 1 or 2 alpha characters, followed by 6 numeric
characters (the 2 extra characters at the end identifies them as
subassets)

I hope this is clearer.

 

[theCityLight]

Hi,

     Yes, that clarifies.  Try this:

SELECT A.AssetTrim
FROM 01EN AS A
WHERE (((Len([AssetTrim]))>=7+IIf(Left$([AssetTrim],2)="BD",2,0)) AND((Not
Exists (select * from 01EN as B where B.AssetTrim = Left$(A.AssetTrim,
Len([A].[AssetTrim]) - 2)))=True));

                   Clifford Bass

Apologies for my lack of clarity..

Answering your question, A1234(56) and  AB1234(56) will be subassets
according to the design of the records in my table.

In my table only assets starting with BD have their parents assets
with the structure: 2 alpha characters and  6 numeric characters and
their sub assets with structure:  2 alpha characters and 8 numeric
characters (the 2 extra characters at the end identifies them as
subassets)

For all other assets,
the structure is
Parent record - 1 or 2 alpha characters, followed by 4 numeric
characters

Subasset record - 1 or 2 alpha characters, followed by 6 numeric
characters (the 2 extra characters at the end identifies them as
subassets)

I hope this is clearer.- Hide quoted text -

- Show quoted text -

Thanks for your help! I'm still analyzing the results. The Select
query took about 5-8 seconds to generate the results which is fine but
I could not copy into a table so I switched to the design view and
saved the Query as a make Table Query. It's about 30 minutes now and
the query is still running.
Any ideas to get the data faster into a table ??

Thanks!

 

[Clifford Bass]

Hi,

You are welcome. I take it that it is a huge table?

Is this a one or two-time sort of thing or an ongoing activity? If an
ongoing activity, it may make sense to do it in VBA code. Essentially what
you would do is a query that sorts the data by the AssetTrim field and then
as you move from record to record, keep track of the prior row's value, or
more accurately, the prior parent's value and compare it with the current
value. I am short on details there.

Clifford Bass

 

[theCityLight]

Hi,

     You are welcome.  I take it that it is a huge table?

     Is this a one or two-time sort of thing or an ongoing activity?  If an
ongoing activity, it may make sense to do it in VBA code.  Essentially what
you would do is a query that sorts the data by the AssetTrim field and then
as you move from record to record, keep track of the prior row's value, or
more accurately, the prior parent's value and compare it with the current
value.  I am short on details there.

                           Clifford Bass






- Show quoted text -

Thanks for your patience. My Asset table has about 70,000 records. I
will run this query about 2 or 3 times in a month.

 

[Clifford Bass]

Hi,

That being the case, it would make sense to look at doing it in
code--see if that improves things. Also, I don't know if the make table
query creates indexes before adding information to the table. If it does,
that can bog things down. You could try creating an indexless table and try
an append query instead.

Good luck!

Clifford Bass