query issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Selby - 16 Apr 2008 15:52 GMT

I have a query based on three different tables. All three of my tables are
correct. If I have more than one contact set up for a customer number, when
the query runs it will duplicate each item for every contact that is set up.

Table 1 - Bid Customer Info - Contact Info is auto number and primary key;
customer number, contact name and contact phone
Table 2 - Bid Number Info - Bid ID #, Customer Number, contact name
Table 3 - Bid Item Info - Bid Item ID (auto number), Bid ID Number, item, qty

I am new to setting up a database and really don't know what to look for
within the query set-up to fix the problem. Any help would be appreciated.

Reply to this Message

Golfinray - 16 Apr 2008 20:40 GMT

That is almost always because you relationships are incorrect. You may even
have to go back and put autonumber promary keys in your tables. Then go to
relationships and connect you tables. Now when you build the query, connect
the tables properly and it should run ok. Make sure you are not trying to
connect two tables on completely different data. Look in help for
relationships.

> I have a query based on three different tables. All three of my tables are
> correct. If I have more than one contact set up for a customer number, when
[quoted text clipped - 7 lines]
> I am new to setting up a database and really don't know what to look for
> within the query set-up to fix the problem. Any help would be appreciated.

Reply to this Message

Margaret Bartley - 16 Apr 2008 22:47 GMT

>I have a query based on three different tables. All three of my tables are
> correct. If I have more than one contact set up for a customer number,
[quoted text clipped - 7 lines]
> Table 3 - Bid Item Info - Bid Item ID (auto number), Bid ID Number, item,
> qty

If you have three contacts fro a customer, and you have the relationship set
up to show all records where the customer numbers match, you will get three
records.

If you want to see the contact name and phone number for specific bids or a
bid items, you will have to put the ContactID in Table 2, which I assume is
the Primary Key of Table 1, and link Table 2 to Table 1 using that
relationship.

Reply to this Message

Selby - 17 Apr 2008 14:46 GMT

thanks for all of the info - I did have an extra table in the query that I
did not need and when I took it out it cleared up the duplicate items.

I am now trying to run a query that is running correctly with "old data".
When I enter new data, it does not show up in the query and I get a message
at the bottom of my screen that says " this recordset is not updatable". Any
ideas??

> >I have a query based on three different tables. All three of my tables are
> > correct. If I have more than one contact set up for a customer number,
[quoted text clipped - 16 lines]
> the Primary Key of Table 1, and link Table 2 to Table 1 using that
> relationship.

Reply to this Message

Tom Wickerath - 21 Apr 2008 09:04 GMT

See Access MVP Allen Browne's page on this subject:

Why is my query read-only?
http://allenbrowne.com/ser-61.html

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

> thanks for all of the info - I did have an extra table in the query that I
> did not need and when I took it out it cleared up the duplicate items.
[quoted text clipped - 3 lines]
> at the bottom of my screen that says " this recordset is not updatable". Any
> ideas??

Reply to this Message

NetworkTrade - 16 Apr 2008 22:50 GMT

query results are never wrong. they may be not what you expect or
want...but they are almost never wrong....it is giving you what your query
logic is asking for

if the duplicate records are absolutely duplicate in every field...then you
can eliminate this by modifying it to a Distinct query; in design view, and
with nothing highlighted select 'View' from the menu bar and then
'Properties' you will see the option for 'Unique Values' change this to
yes and see if it works....

another approach is to define the relationship in the query design view. If
one table has all the records - instead of just a line to the other table
(representing the linked field) click on that line and select a join so that
it ends up looking like an arrow from the table with everything to the other
table. Do for both.... this approach maybe will work also depending on your
tables, fields, etc...one can's say for sure generically.....

Signature

NTC

Reply to this Message