Queries, relationships and duplicate creation that I can't remove.

[kcmiuser]

Okay let me start by saying that I'm not an experienced access user, although I would like to be, but job duties don't permit me the time to really learn it. So there are a lot of areas that confound me, but I do use it occasionally to create small projects to make my department run a bit smoother. So the problem my having is as follows: I have two pass-through queries that are pulling data from a very large ERP database. I need to create a make table query that will use feels from both of these, the problem is now that I'm getting duplicates. The first query contains data that has to do with material requirements planning and action messages, I need all of the fields that are in this query for the make table; second query pulls inventory data but has a small amount of fields but these are necessary in the make table for creating parameters. I know I'm getting duplicates because I can see them in the make table record count and also see them visually, so do I have to create a relationship between these two queries in order to eliminate the duplicates without losing all of the message data from the first query-can you create relationships between queries or is that only for tables.. I can use any help anyone can give me because I'm in areas now that I just don't understand very well. Thank you

[June7]

The join in the query relates records. Do these tables have a 1-to-many relationship?

[kcmiuser]

Yes they do, table one (action messages) can have several for one part number, the part tables is all the individual parts(what I am getting from here is code fields that I will use later as parameters for filtering the messages by a code). Example, part 'fanmotor' can have code 'a5', but I can have several action messages for that part for different dates, so I will list part fan motor with all the action messages and corresponding dates pertaining to it. Now, I can have several parts with the same code, so then I can list all the parts for that code with all of there messages on a report. Would that mean a 1 to many relationship, this is an area of access I am a bit fogging about. Hope this helps. Thanks for your help

[June7]

Yes, that is 1-to-many and the cause of 'duplicate' records. What is needed for the 'parameters'? If you want to provide db for analysis, follow instructions at bottom of my post.

[kcmiuser]

The end user will use parameters to select various codes for reports, this is the only way I can figure out how to get what I want, I am sure there are more methodologies, I am just too much of a newb . I will send this later today, maybe, depends on how bad my 'spring fever' gets this afternoon since it's a long weekend. If I don't please have a nice Memorial weekend and we will start again next week.

[orange]

There are a number of tutorials at Martin Green's site.\
Here's a link http://www.fontstuff.com/access/acctut19.htm