Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    9

    Unanswered: Query and preserve all records from all tables?

    I have a access Db and struggle with a Query that I want to preserve all records from all tables columns in.
    Anyhow I tried put one "cnt" field in all tables with the value of "1"
    and did a join.
    Anyhow the query becomes huge as it multiplies with all the records from all tables..
    and the perfomance is terrible and the amount of rows become huge.
    The db has 14 tables with only one column each.. and different amount of rows. but usally 5 to 35rows

    How is the best way to go about this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of knowing what you design is, what the problems are I haven't got a scooby.

    addiong a column with a 1 in it is hardly going to cause what you report, there is somethign else that is the cause of the problem. incidentally if this is a yes/no/boolean value then use a boolean datatype AND use the intrinsic vb constants vbYes or vbNo.

    either your joins are malformed, your data model is not normalised. but short of knowing what the SQL is and what the tables are then its unlikely that anyone here can give you an answer unless they are Psychic. personally I'm more psychotic than psychic, but each to their own.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2008
    Posts
    9
    Hello thanks for your reply,
    The design at this point is "what design?" I just have 14 tables with only a single column in each table.
    And ofcource this tryout column "cnt" (integer) I tried to have a mutual match (1) for all records in all the tables.
    But you suggest to instead use a bool value to set for each record in each table? why would that be better

    The tables does not have any relation to eachother, I just want to display them in one single table to be able to grab the info easier in a database connection in visual studio (Now I do this for each table.)

    What I understand one can not do full outer join using access which seem to be the usual practice when want to preserve all records in all tables.

    The ideal resulting query table would be a table with only 35 rows
    and the tables with least records are filled in with blanks down to row 35 beyond their recs end, If you understand?
    Code:
    So now I have
    Table1:
    col1  col2
    red        1
    blue       1
    brown      1
    
    Table2
    Col1               Col2
    bmw                 1 
    chrysler            1
    ford                1
    toyota              1
    holden              1   
    saab                1
    nissan              1
    subaru              1 
    gm                  1  
    volkswagen          1 
    
    Table 3
    Col1               Col2
    2001                 1 
    2009                 1
    2003                 1
    
    
    I would like a resulting query that display this:
    Col1        Col2            Col3 
    Red        bmw            2001
    Blue      chrysler        2009  
    Brown     ford            2003 
    ""        toyota          ""
    ""        holden          ""
    ""        saab            ""
    ""        nissan          ""  
    ""        subaru          ""
    ""        gm               ""

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what links what elements in what tables
    Assuming your requirements is what you indicate:-

    Id want a table for
    manufacturers 'eg ford, VW, Jaguar, Land Rover etc...
    models 'eg Polo, Golf, Passat, Phaeton, Evoque
    build ' eg S, RS32, GT, GTi etc.
    options 'eg base radio, standard radio, CD Radio, CD Changer, DAB Radio
    Colours
    ModelOptions 'identifies what options are available (and / or what options are included)
    Model Colours ' identifies what colours are available on which model builds
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2008
    Posts
    9
    no there is no correlation between the tables what so ever, that is not important.
    One table can contain chese types ond the other cars, the above was just an example

    I just want to fetch all records from all tables into one with a SQL query, and make it as lightwight as possible to open (as few rows as possible)

    Thanks/M

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you've given an analogy thats pants and or not relevant
    there is no link between the tables
    there's precious little information to go on as to
    How is the best way to go about this?
    on what you've said so far I'd do a union all and hope somewhere in the morass of rfows that will be returned is the data you want. but as to how you identify the information that is relevant from the rest of the crap is beyond me.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2008
    Posts
    9
    ok hope for the right data is not good enough
    The plan is to use the query column values for items in comboboxes, it should not be data from different tables, insted one combobox for each table

    As I understand UNION ALL would end up joining up fields that are similar from two tables, and here there are no similar fields...
    With UNION ALL you would end up having year, cars, chese types in the same column right? which is not desired.

    See my example above in the code section with the Table1, Table2, Table3 and the resulting Query table, I don really know how I can clarify more than from the above example.

    Any method is good, even putting an extra field of some kind to break this nut.


    Thanks for your effort.


    Here is my query with join of a added "cnt" column that I put in all tables, and put in a mutual "1" to get all.
    It does what desired but it becoms very slow with all tre resulting duplicate rows.. Maby one can use som kind of *Distinct parameter? or setting the query not to have duplicate values in each field?

    Code:
    SELECT Ålder.*, Åtgärder.*, Beställare.*, [Diameter brösthöjd].*
    FROM [Diameter brösthöjd] INNER JOIN (Beställare INNER JOIN (Åtgärder INNER JOIN Ålder ON Åtgärder.cnt = Ålder.cnt) ON Beställare.cnt = Åtgärder.cnt) ON [Diameter brösthöjd].cnt = Beställare.cnt;

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if these tables provide the data to various combo boxes then use a separate table AND query per combo / listbox.

    I don really know how I can clarify more than from the above example.
    I don't really understand what you are trying to achieve, so I doubt I can be of any assistance.

    I wonder if you are trying to make something more 'efficient' without understanding whether the application already has performance issues. keep data in discrete datastores. use joins when there is something common between the tables.


    I don't understand what your cnt column is about.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2008
    Posts
    9
    Quote Originally Posted by healdem View Post
    if these tables provide the data to various combo boxes then use a separate table AND query per combo / listbox.
    That is what I do at this time, but that gives me 14 datasets 14 tablebindingsouces and 14 tableadapters in the form, so I figured it would be a wise move to try to merge all the tables into one performencewise.

    Quote Originally Posted by healdem View Post
    I don't really understand what you are trying to achieve, so I doubt I can be of any assistance.
    I dont know either, I have tried to put it out for you,
    1What we have and 2 what the query should result in.
    Appriciate the effort though

    Quote Originally Posted by healdem View Post
    I wonder if you are trying to make something more 'efficient' without understanding whether the application already has performance issues. keep data in discrete datastores. use joins when there is something common between the tables.
    I dont know for sure if its possible to make things a bit swifter, but I figured it was worth a go as it is kind of heavy to open up the form right now.


    Quote Originally Posted by healdem View Post
    I don't understand what your cnt column is about.
    I tried a different approach by changeing the cnt column to a counter field (autonumber; 1,2,3,4 for each row for this column then do a join of all the tables with this field as common.

    Then set up all the joins so I keep ALL records from the table thas has the most records and the other table only where matches.

    The result end up like the desired one in my example of cource.
    But I bet there are some smarter way to do this though.

    There is a problem with this approach though,
    if one delete a row for example nr row nr4 from the maintable in the join (the one with the most records) then row nr4 from the other tables will be gone too in the resulting query as there wont be a match.
    So I have to figure out what to do about that...

    Ideally in such a case one would want the cnt in the maintable to regenerate from row 1 to row++ without any "missing numbers".

Posting Permissions

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