Using relation or criteria in multiple table query (All)

Topic

New Reply

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 …
Thanks for any feedback & a happy newyear to all!

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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?

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]That would make the discussion easier.

Reply | Quote

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?

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]That would make the discussion easier.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote