DB query - Compare 2 colums in 2 tables - result = numbers not be.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DB Queen (Not) - 04 Apr 2005 16:17 GMT

I just want to draw out the numbers in my database that are not being used so
that they can go in a separate query or table so they can be reused.
Anyone any ideas how to program this??

Thanks

Reply to this Message

John Vinson - 04 Apr 2005 17:10 GMT

>I just want to draw out the numbers in my database that are not being used so
>that they can go in a separate query or table so they can be reused.
>Anyone any ideas how to program this??
>
>Thanks

If the number is a Long Integer, then there are 4294967296 possible
values, most of which are probably not being used. Could you explain
what you mean by "the numbers in my database" and what constitutes
being "used"?

John W. Vinson[MVP]

Reply to this Message

Pat Hartman - 04 Apr 2005 18:59 GMT

Use a Left Join instead of an Inner join and add selection criteria that
selects rows where the primary key on the right side table is null:

Select tblA.* From tblA Left Join tblB on tblA.Fld1 = tblB.Fld1
Where tblB.Fld1 Is Null;

You can use the unmatched query wizard to build this query for you.

> I just want to draw out the numbers in my database that are not being used so
> that they can go in a separate query or table so they can be reused.
> Anyone any ideas how to program this??
>
> Thanks

Reply to this Message

DB Queen (Not) - 05 Apr 2005 13:51 GMT

I have files numbered 1-100 say with names and addresses etc also in the
record. Sometimes these details need to be archived but the number remains
in the database for re - use. In female terms I want to say if column b-g is
blank then column a copy to query 1. (if that makes sense??) Thanks guys!

> Use a Left Join instead of an Inner join and add selection criteria that
> selects rows where the primary key on the right side table is null:
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

Jeff Boyce - 05 Apr 2005 14:57 GMT

We're not there, and can't see your database design unless you describe it
to us.

We may have a different meaning than you do for "files numbered 1-100" ...
what do you mean?

Why do you feel it is necessary to "archive" (and what do YOU mean by that)
some of the "details" (do you mean a person's name can be a "detail"?)?

What "number"? What are you referring to when you say "the number remains
in the database for re-use" (and what does "re-use" mean to you)?

In Access, you don't copy a column (?field) to a query. You can create a
query that displays a column (or updates).

Signature

More info, please ...

Jeff Boyce
<Access MVP>

> I have files numbered 1-100 say with names and addresses etc also in the
> record. Sometimes these details need to be archived but the number remains
[quoted text clipped - 15 lines]
> > >
> > > Thanks

Reply to this Message