Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

URGENT: TOUGH QUESTION TO MAKE IT WORK

Discussion in 'Information Technology' started by Broadway, Nov 10, 2005.

  1. Broadway

    Broadway
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    URGENT PLZZ HELP: KAAM LUST BODY MASSAGE SPA ISSUE Sikh Youth Jul 9, 2015
    Sikh News Spate Of Attacks By Islamist Insurgents Rattle Sikh Community In Pakistan Breaking News Apr 19, 2015
    SciTech Urgent Need to Remove Space Debris Breaking News Apr 25, 2013
    Urgent! Need help with some gender equality questions! Questions and Answers Jan 7, 2013
    General URGENT! Video on reality of sexual grooming gangs in the UK - please share Hard Talk Nov 21, 2012

  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  4. Broadway

    Broadway
    Expand Collapse
    Guest

    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.

    "Nikos Yannacopoulos" wrote:

    > 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
    >
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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.

    --
    Brendan Reynolds

    "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    news:3F540937-0C9B-494F-91D2-BEB8F879B1BA@microsoft.com...
    > 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.
    >
    > "Nikos Yannacopoulos" wrote:
    >
    >> 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
    >>
     
  6. Broadway

    Broadway
    Expand Collapse
    Guest

    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?



    "Brendan Reynolds" wrote:

    >
    > 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.
    >
    > --
    > Brendan Reynolds
    >
    > "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    > news:3F540937-0C9B-494F-91D2-BEB8F879B1BA@microsoft.com...
    > > 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.
    > >
    > > "Nikos Yannacopoulos" wrote:
    > >
    > >> 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
    > >>

    >
    >
    >
     
  7. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

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

    --
    Brendan Reynolds

    "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    news:F9720A78-3E0A-48D2-994E-1D98C6C4DD39@microsoft.com...
    >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?
    >
    >
    >
    > "Brendan Reynolds" wrote:
    >
    >>
    >> 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.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >> "Broadway" <Broadway@discussions.microsoft.com> wrote in message
    >> news:3F540937-0C9B-494F-91D2-BEB8F879B1BA@microsoft.com...
    >> > 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.
    >> >
    >> > "Nikos Yannacopoulos" wrote:
    >> >
    >> >> 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
    >> >>

    >>
    >>
    >>
     
  8. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest


    > The table names aren't containing any data,

    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

    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.

    "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.

    This only takes a simple filtering query anyway! Look at Brendan
    Reynold's reply, and take his advice.

    HTH,
    Nikos
     
  9. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 10 Nov 2005 02:45:02 -0800, "Broadway"
    <Broadway@discussions.microsoft.com> wrote:

    >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.


    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]
     
  11. John Gray

    John Gray
    Expand Collapse
    Guest

    > 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...
    > 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.
    >
     

Share This Page