Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: Make table query....

    I have large table that includes a field city.

    There is about 140 cities total.

    Must make those 140 tables that include data for those cities (every table has to have just it's data, data for the city). New tables has to have different names (Table name should be the city name).

    I started to do this with make table query. But - I have problems with parameters, and big problem with - how to name table using city name.

    Anybody has good and simple solution for this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by computerforce
    Anybody has good and simple solution for this?
    Yes - don't

    Ignoring the thought that you want 140 new tables - what is your business requirement? What do you need to do? (by that I don't mean "I need to create 140 tables" but what is the eventual goal? What are you attmepting to produce?)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Analytical data should be stored in 140 tables, divided, then send to those 140 cities.

    Every single city has to see just it's data.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    As Pootle intimated, we do not know what data structure you have/want, but would it be practical in your case to have a 'tblCity' listing the 140 cities (PK = ID) an a second 'tblCityData' table holding all the city data (FK = CityID) and just relate the two, assuming the data fields are (mostly) the same for each city ?? !!

    Just a thought ...
    MTB

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    The main point is that the huge table - few millions of records has to be divided into tables, based on cityname.

    I am thinking now about a sql query - handmade, with a while loop.

    Any idea?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by computerforce
    The main point is that the huge table - few millions of records has to be divided into tables, based on cityname.
    That's my point - most of the time it doesn't.

    Quick question - have you planned ahead to what your queries will look like when you query your data? Do you plan on writing 140 queries for each data enquiry you need to make?
    Also - if you are pushing into the territory of several millions of records then you may be close to hitting the limitations of JET as an RDBMS and may need to consider another platform.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to be clear - the answer to the question is trivial. It is just that most times this question is asked the question itself is wrong.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2004
    Posts
    84
    That's the fact.
    I am trying to find simple solution.

    The database is at SQL Server. The main (huge) table is at SQL Server.

    I link MS Access to the SQL Server, and I plan to use query against the sql server to make MS Access tables in MS Access database. MS Access tables will have less number of fields, and would not be so big.

    Any solution?

  9. #9
    Join Date
    Mar 2006
    Posts
    163
    I agree with the other members that you really shouldn't be doing this.

    But it can be done using code.

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by MikeTheBike
    Hi

    As Pootle intimated, we do not know what data structure you have/want, but would it be practical in your case to have a 'tblCity' listing the 140 cities (PK = ID) an a second 'tblCityData' table holding all the city data (FK = CityID) and just relate the two, assuming the data fields are (mostly) the same for each city ?? !!

    Just a thought ...
    MTB
    I'm in agreement with the other members as well. What you described in itself would make for a not so easy task as it should be. Mike stated it well and will serve the same purpose. That is how the database should work for the most part, and the end results would be what you need. That way you always can retrieve the data according to the query created, possibly have a popup form to input your criteria and have it pull the data. Creating so many tables is extra work that need not be, besides would be so difficult to maintain if anything went wrong. Of course, everyone has their own individual way to work things, but the above are the more logical, from a RDMS point of view.

    BUD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •