Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Using relation or criteria in multiple table query (All)

    Hey,
    can anyone give some remarks on this database/table/query design...?
    Imagine two databases...
    e.g. sales
    (1) tblCatalogue = IDcatalogue + IDproduct + price + year + ...
    (2) tblProducts = IDproduct + name + ...
    (3) tblSales = IDsales + IDproduct + IDorder + ...
    (4) tblOrders = IDorder + year + ...
    => query example: total $ to be paid for order ...
    e.g. cycling
    (1) tblCyclistRanking = IDranking + IDcyclist + MarketValue + year + ...
    (2) tblCyclists = IDcyclist + name + ...
    (3) tblTeamMembers = IDteam + IDcyclist + ...
    (4) tblTeams = IDteam + name + year +...
    => query example: total market value of Team ... in year ...
    In both cases, many queries(/forms/reports) combine information of tables 1 & 3. To achieve this, I used a query relating those three tables with (1) the usual key - fields (providing the links in the chain) AND (2) the year - field (linking the 'ends' of the chain).
    In Access (97?), however, such queries proved to be quite unstable (certainly when they got more complex, involving more tables,...). Is this an Access-problem or did I do something wrong here?
    E.g. as an alternative, I considered also
    - replacing the relation between table1!year and table2!year with a criterium in the query design grid, equalling table1!year = table2!year (but this seems to be too weird to be a wise solution)
    - when the query result only deals with only one year: using a parameter in the criteria equalling both year fields = YYYY (e.g. referring to a field/control on a form from which the query is launched).
    Or did I make a mistake in the database/table design? (Some friend pointed me once at the 'cyclic relationship' I created between the 3 tables: table1!year => table3!year, table3!ID2 => table2!ID2, table2!ID1 => table1!ID1.)

    I made both databases some years ago and they might be subject to further development soon - so if it's a bad design, I'ld be happy to 'repair' it before I continue <img src=/S/duck.gif border=0 alt=duck width=23 height=23> ...
    Thanks for any feedback & a happy newyear to all!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using relation or criteria in multiple table query (All)

    Can you explain what you mean by "such queries proved to be quite unstable" ?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Using relation or criteria in multiple table query (All)

    Hans,
    I think that I got an (error?) message when opening those queries in design view. I forgot the content since it's been quite a while ago (and I haven't been able to reproduce the error - possibly because I was working in Access 97 back then, and now in Access XP). It could have been something like: ...too complex... Further, I think that I had to restore the second relationship (between the year-fields, which Access appeared to have deleted!?). Finally, I think that I also wasn't even capable of storing and closing the query (sometimes it did, mostly it didn't). That's what I meant with unstable: I had been able to create the queries once, but afterwards Access 97 didn't 'accept' them anymore (most of the time).

    So, it might be better if I rephrase my question:
    - do you think the datastructure (the location of the year-fields in the tables) I used is OK? If so, I can be confident & continue...
    - by the way: could the problems I (tried to) describe have been related to Access 97?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using relation or criteria in multiple table query (All)

    I don't think that Access 97 is the culprit. Could you perhaps post a small demo database, with a set of tables an one of the queries you tried to create?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]That would make the discussion easier.

Posting Permissions

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