Help please with huge database.

[CanaKiwi]

Hi

I'm a newbie to access and I'm struggling with some data and how to update
it.

I have a huge DB that consists of 10 tables, each representing a year of
data and approximately 300,000 records in each year. The tables are not yet
linked in any way but they consist of the same fields from year to year. The
fields are

Code
File#
Name
Address
Details.

For this exercise I'm only really interested in the File# and the Code. The
Code field can either be an "Addition" or a "Deletion" and I'm only really
interested in the "Open" files, ie the ones for which there is an addition
but no deletion.

A file# could be added one year and could be deleted in that same year or it
could be deleted 2 years down the track. Obviously if it is deleted the same
year, then there will be 2 records in the respective annual table with the
same file#. If it is deleted in a subsequent year then I'd consider it
closed and I have no further interest in it.

Here's what I would like to do.

I want to somehow view only the "Open" records. I consider an open record
to be one which was added and was not later deleted.....I do not know how to
do this.

I think I want to select the first file# in the DB that is coded as an
"Addition" then search each and every table for the same file# that is a
"Deletion". If I cannot find a corresponding entry in the same or another
table I want to flag it somehow.

Any help or pointers are greatly appreciated.

TIA
BOZO

 

[Guest]

The first thing I would do is either create a union query (qryAllTables) of
all the tables ,
Select * from table 1
Union All
Select * from table2

etc.
At this point every file# in your query woudl necessarily have to have an
'Addition' entry or it wouldn't be there. You want to select all files from
this query that don't have a 'Deletion' entry.

Select File# from qryAllTables where file# not in (select File# from
qryAllTables where Code = 'Deletion')

 

[John W. Vinson]

Hi

I'm a newbie to access and I'm struggling with some data and how to update
it.

I have a huge DB that consists of 10 tables, each representing a year of
data and approximately 300,000 records in each year. The tables are not yet
linked in any way but they consist of the same fields from year to year. The
fields are

3 million records? Respectably large but not huge.

If the tables have the same data in each, then they should not be linked at
all - there is probably nothing to link them to.

Code
File#
Name
Address
Details.

For this exercise I'm only really interested in the File# and the Code. The
Code field can either be an "Addition" or a "Deletion" and I'm only really
interested in the "Open" files, ie the ones for which there is an addition
but no deletion.

A file# could be added one year and could be deleted in that same year or it
could be deleted 2 years down the track. Obviously if it is deleted the same
year, then there will be 2 records in the respective annual table with the
same file#. If it is deleted in a subsequent year then I'd consider it
closed and I have no further interest in it.

It sounds like this should be one, 3 million row table - NOT ten yearly
tables. Do you care about the date? or if there are two records for a File# in
2004 (one an Addition and the other a Deletion) do you just want to discard
the record?

Here's what I would like to do.

I want to somehow view only the "Open" records. I consider an open record
to be one which was added and was not later deleted.....I do not know how to
do this.

I think I want to select the first file# in the DB that is coded as an
"Addition" then search each and every table for the same file# that is a
"Deletion". If I cannot find a corresponding entry in the same or another
table I want to flag it somehow.

I'd suggest creating a new .mdb file. Use File... Get External Data... Link to
link to the current database. If you don't care what year the data came from,
just import one of the annual tables. Open it in design view; flip the order
of File# and Code, and make them a joint Primary Key.

Then use File... Get External Data... Link to link to the existing database
and run nine Append queries, appending from the other nine tables into this
new table. You'll end up with a 3 million odd row table.

To trim this table down to only Open files, create a Delete Query:

DELETE A.* FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.[File#] = [B.File#]
WHERE A.Code = "Addition"
AND B.Code = "Deletion";

to delete all "additions" which have a corresponding "deletion".

Then Compact your database and delete the deletions:

DELETE yourtable.*
FROM yourtable
WHERE Code = "Deletion";

Compact again and you should have only open files.

John W. Vinson [MVP]

 

[CanaKiwi]

Thank you both very much for your quick responses.

Hi

I'm a newbie to access and I'm struggling with some data and how to update
it.

I have a huge DB that consists of 10 tables, each representing a year of
data and approximately 300,000 records in each year. The tables are not
yet
linked in any way but they consist of the same fields from year to year.
The
fields are

3 million records? Respectably large but not huge.

If the tables have the same data in each, then they should not be linked
at
all - there is probably nothing to link them to.

Code
File#
Name
Address
Details.

For this exercise I'm only really interested in the File# and the Code.
The
Code field can either be an "Addition" or a "Deletion" and I'm only really
interested in the "Open" files, ie the ones for which there is an addition
but no deletion.

A file# could be added one year and could be deleted in that same year or
it
could be deleted 2 years down the track. Obviously if it is deleted the
same
year, then there will be 2 records in the respective annual table with the
same file#. If it is deleted in a subsequent year then I'd consider it
closed and I have no further interest in it.

It sounds like this should be one, 3 million row table - NOT ten yearly
tables. Do you care about the date? or if there are two records for a
File# in
2004 (one an Addition and the other a Deletion) do you just want to
discard
the record?

Here's what I would like to do.

I want to somehow view only the "Open" records. I consider an open record
to be one which was added and was not later deleted.....I do not know how
to
do this.

I think I want to select the first file# in the DB that is coded as an
"Addition" then search each and every table for the same file# that is a
"Deletion". If I cannot find a corresponding entry in the same or another
table I want to flag it somehow.

I'd suggest creating a new .mdb file. Use File... Get External Data...
Link to
link to the current database. If you don't care what year the data came
from,
just import one of the annual tables. Open it in design view; flip the
order
of File# and Code, and make them a joint Primary Key.

Then use File... Get External Data... Link to link to the existing
database
and run nine Append queries, appending from the other nine tables into
this
new table. You'll end up with a 3 million odd row table.

To trim this table down to only Open files, create a Delete Query:

DELETE A.* FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.[File#] = [B.File#]
WHERE A.Code = "Addition"
AND B.Code = "Deletion";

to delete all "additions" which have a corresponding "deletion".

Then Compact your database and delete the deletions:

DELETE yourtable.*
FROM yourtable
WHERE Code = "Deletion";

Compact again and you should have only open files.

John W. Vinson [MVP]