query returning either nothing or multiples of records

Tech-Archive recommends: Fix windows errors by optimizing your registry

I?m having an extremely large amount of trouble with a query.

My trouble begins when I try to do a query based on the ?date of research?
field contained in the ?cost? table. The query will only return data when the
Junction table between ?Industry classification? and ?Generic asset
description? is not included in the relationship query design view. It
doesn?t even matter if there is not a field from this table included in the
query, if the relationship is displayed the query returns nothing. Also when
I take it out the query still only returns the data from a couple of records
multiple amounts of times. Obviously there should be a number of valuations
for each asset but these should all have different cost ID?s yet in the query
there are a number of results for each asset with the same cost ID.

Details:
My database is designed to store information regarding the valuation of plant
and machinery. There is a transaction table that contains specific asset
information (model, description etc). There are several simple attached
tables for manufacturer, client and cost (separated to support the fact that
one asset can have many valuations). The area that I think I may have not
built correctly is the relationship between ?generic asset description? table
and the ?industry classification? table. These two tables have a Many to many
relationship between each other and the ?industry classification? table has a
many to many relationship with the ?transaction? table, the ?generic asset?
table has a standard one to many relationship with the ?transaction? table.

Also the cost table links to the transaction table using the transaction ID
not the Cost ID as there will be many Cost ID?s for each Transaction ID. I?m
not sure if this may also be the cause of some of my trouble.

I fear that I may have the Many to Many relationships very wrong Please help
me!!!


--
Message posted via http://www.accessmonster.com
.

Relevant Pages

  • Re: Covering indexes versus column order in Delaney
    ... yes - if the index is not useful to ANY query or statement, ... the cost of a table scan (or ... a clustered index scan) is 10,000 page reads (plus some overhead ... >consisting of account number, transaction date, and transaction sequence ...
    (microsoft.public.sqlserver.programming)
  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)