URGENT: TOUGH QUESTION TO MAKE IT WORK

B

Broadway

Ok, so here is the issue. My database builds tables based on the unit the
person belongs to, then whoever is inputting data can change whatever data
they need to for a person by scrolling through AFTER they pick the unit. The
problem is, I have been asked for someone to search ALL the tables (no
relationship between any table) for ANY criteria (I plan on using a form to
request what the user wants to sort by). The problem is, I don't know how to
call all of the tables together for one block of records matching the
criteria. I am stumped for an idea! I have no code already written for this
portion of the program. Any help will be appreciated as soon as possible.
 
N

Nikos Yannacopoulos

I understand all the tables you refer to have the same structure, right?

While you could overcome your problem by using a UNION query (look it up
in Access help) to bring together the records from all the tables, your
approach is fundamentally wrong, and very likely to create more problems
as you proceed with your development. Under your current design, you are
essentially using the table names to "store information", i.e. the
department for which they contain data; what you should be doing, is to
use a single table for all departments, adding one more field for the
department; then, for each user you would just add records with the
department code in that field, or give them access to existing records
filtering on the department field through a query; when all records for
all departmets are required, you just don't filter on thedepartment field.

HTH,
Nikos
 
B

Broadway

Actually,
The table names aren't containing any data, but rather imagine this program
is being given to 4 company sized units, and the personnel sections put in
their data for each company's personnel, then they export their table named
for their company (the only one they will really use.) They then send their
exported table to the battalion, which has 4 company tables, which they use
to view for all of the battalion, then the battalion sends up their 4 company
tables to the brigade, (which has 4 battalions). Now, if I tell you that
these records will be updated daily, and I tell you each company has 60 - 100
people, that's a lot of records to compile into one table, where it seems it
would be cleaner and easier to update the records by simply importing from
company tables. This way also keeps data companies don't need from a
different battalion out of their lane.
 
B

Brendan Reynolds

With one table, to divide the data by company, you would use queries such as
....

SELECT * FROM YourTable WHERE CompanyID = CurrentCompanyID

With your current design you will have to do something like this ...

SELECT * FROM Company01
UNION SELECT * FROM Company02
UNION SELECT * FROM Company03
UNION SELECT * FROM Company04
UNION SELECT * FROM Company05
UNION SELECT * FROM Company06
UNION SELECT * FROM Company07
UNION SELECT * FROM Company08
UNION SELECT * FROM Company09
UNION SELECT * FROM Company10
UNION SELECT * FROM Company11
UNION SELECT * FROM Company12
UNION SELECT * FROM Company13
UNION SELECT * FROM Company14
UNION SELECT * FROM Company15
UNION SELECT * FROM Company16

I strongly advise using one table.
 
B

Broadway

I must admit, queries are my weak link in access, I hadn't even heard of
union queries until today. what about this:

I have built another table as a master table, to which i first delete all
records from (to prevent ballooning of the program) append all the records
from all of the tables by going through the table def. This table will be
there for the rest of the time the program is running, then as the program
closes, the program deletes all of the records from the table (once again to
prevent bloating). This way, I can update the master table every single time
there is any change.

What do you think?
 
B

Brendan Reynolds

I can only repeat what I have already said - I strongly advise using one
table.
 
N

Nikos Yannacopoulos

The table names aren't containing any data,
Click to expand...
Implicitly they are! Assuming you could somehow ahve all tables named
the same, how would you be able to tell which contains what? It's the
name that tells you this!

...and I tell you each company has 60 - 100
people, that's a lot of records to compile into one table
Click to expand...
One of my back-ends, in production for about three years now, has about
750,000 (and growing) in one table (and I'm sure others in the NG could
give you bigger numbers), while the performance is excellent. By
comparison, your numbers are less than peanuts.

where it seems it would be cleaner and easier to update the records
by simply importing from company tables.
Click to expand...
"Seems" is a very good choice of word here... it may look easier right
now, but will definitely get you into trouble further down your
development. As for "cleaner", it depends on the definition; to me,
cleaner is less objects and more structure.

This way also keeps data companies don't need from a
different battalion out of their lane.
Click to expand...
This only takes a simple filtering query anyway! Look at Brendan
Reynold's reply, and take his advice.

HTH,
Nikos
 
N

Nikos Yannacopoulos

What you refer to as "ballooning" is generally referred to as "bloating"
in Access terms, and unnecessary deletes and re-writes are an excellent
way to boost it!

Nikos
 
J

John Vinson

The table names aren't containing any data, but rather imagine this program
is being given to 4 company sized units, and the personnel sections put in
their data for each company's personnel, then they export their table named
for their company (the only one they will really use.) They then send their
exported table to the battalion, which has 4 company tables, which they use
to view for all of the battalion, then the battalion sends up their 4 company
tables to the brigade, (which has 4 battalions). Now, if I tell you that
these records will be updated daily, and I tell you each company has 60 - 100
people, that's a lot of records to compile into one table, where it seems it
would be cleaner and easier to update the records by simply importing from
company tables. This way also keeps data companies don't need from a
different battalion out of their lane.
Click to expand...

Just to join Nikos and Brendan in their excellent advice:

You're on the wrong track.

If you're assuming that you must have a Company table in order to be
able to export the data for that company, your assumption is
INCORRECT.

It's just as easy - even easier in some ways - to export from a Query.

If you had *one* Personnel table, with an indexed Company field, then
you can easily create a Query which looks, works, tastes and smells
exactly like your current Company tables. And you won't have the
problme which originally prompted you to post.

An Access table would indeed be straining - a bit - to hold the entire
personnel table of the United States Army, but it could do it. 1600
rows (four battalions * four companies * 100 people each) is a very
modest table; and you have that much data in your database ALREADY,
plus the overhead systems data for fifteen unneeded tables. Going to
one table will make your database smaller and more efficient.

John W. Vinson[MVP]
 
J

John Gray

They then send their exported table to the battalion,

This implies there is some physical distance/separation between
the specified groups (Brigade - Battalions - Companies). More
than just the office next door. Is that correct?

Is there a computer network that links the "personnel" people?
How about Internet access?

I see a couple of possibilities...

1. If a network is available, could you not have one central muti-user
database (all personnel in one table)? Someone else will have to
confirm this, but couldn't you use user-level security, to ensure that
different Companies and Battalions see only their own data?
Presumably, the Brigade office would have access to *all* the data.
(see db structure below)

2. If no network, then a multi-tier approach, using sneaker-net, would
work. In your scenario, there would be 16 Company databases
feeding into 4 Battalion databases, which feed into 1 Brigade db.

For simplicity, use the same db structure for all locations (*one*
personnel table -- see db structure below). You could pre-enter
some of the data, like Battalion and Company info.

a) each COMPANY would enter/update person data under its own
section in its own db
b) at the BATTALION level... each Battalion Company delivers a copy
of its db to its Battalion office. The Battalion office would run an
import routine to update its db with the supplied Company data.
c) at the BRIGADE level... each Battalion delivers a copy of its db to
the Brigade office. The Brigade office would run an import routine
to update its db with the supplied Battalion data.

Each Company would have/see only it's own data. Each Battalion
would have/see only the data from it's 4 Companies. The Brigade
would have/see the data from its 4 Battalions (ie: the Brigade would
see *all* the data).

The "import routine" would be an Update query. You don't need to
be adding/deleting tables. Bloating can be taken care of with the
Compact/Repair function.

(Getting a db copied and delivered in a regular timely manner can
be a real pain. If your personnel offices at least have email, you
could use an email data-transfer utility. Schedule it to run after
hours. I did this a few years ago, for a local animal welfare group
that had several shelters in the area. It saved a lot of grief :)


The no-network approach is certainly more complicated, but it can
be just as clean as using a central db on a network. Either way,
searching and reporting would be a much simpler process.

hth,
John


----------------------

DB Structure...

I imagine your database would look something like this:

Relationships
- A Brigade has many Battalions
- Each Battalion has many Companies
- Each Company has many Persons

tblBattalion
BattID - PK
BattName
etc...

tblCompany
CompID - PK
BattID - FK from tblBattalion
CompName
etc...

tblPerson
PersID - PK
CompID - FK from tblCompany
PersFirst
PersLast
etc...

If this really is a military organization, then you probably already
have unique IDs assigned in the organization for each Person,
Company, Battalion. You could use these as the Primary and
Foreign keys.



=======================================
"Broadway" wrote...
 
Top