Error in Query

[FoxProProgrammer]

The Query below causes an error.

SELECT DISTINCT Components.SRUID, SRU.SRU, PartNumbers.PartID
FROM SRU INNER JOIN (PartNumbers INNER JOIN Components ON PartNumbers.PartID = Components.partID) ON SRU.SRUID = Components.SRUID
WHERE (((PartNumbers.PartID)=[lst_PN].[Value]))
ORDER BY Left([SRU],4), Val(IIf(IsNumeric(Mid([SRU],5,1)),Mid([SRU],5),Mid([SRU],4)));

Here's the error:

"ORDER BY clause (Left([SRU],4)) conflicts with DISTINCT."

The explanation in Help says:

You created an SQL statement with an ALL, DISTINCT, or DISTINCTROW predicate and an ORDER BY clause that contains a field not listed in the SELECT statement. Remove the DISTINCT reserved word, or remove the specified field from the ORDER BY clause.

The field [SRU] is in the SELECT statement, so I don't know what Access doesn't like about this. I use the same ORDER BY clause in another Query that doesn't require the DISTINCT clause, and it works there. I am trying to sort by a field of the form AAASNN, where A is a string, S is a space, and N is a number. If I remove the DISTINCT clause, the Query runs, but there are duplicates in the results.

Thanks for your help!


dz

 

[rac2]

One might think that mentioning the field SRU in the ORDER BY clause would count even though it is processed by functions. Apparently not.

Maybe create a view (an Access Query) with the DISTINCT and no ORDER BY. Then create a second query SELECTing from it using the ORDER BY.

I might add that sometimes the DISTINCT "predicate" ( love that technical jargon) is a bandaid solution for an incorrect JOIN. I would explore the reasons for the duplicates and whether the JOIN could be revised to eliminate them. Of course there are situations where the DISTINCT is the only appropriate solution.

Let me know whether the two-step query idea works.

 

[PHV]

A one step way:
SELECT DISTINCT Components.SRUID, SRU.SRU, PartNumbers.PartID
, Left([SRU],4) AS SRU_A, Val(IIf(IsNumeric(Mid([SRU],5,1)),Mid([SRU],5),Mid([SRU],4))) AS SRU_N
FROM SRU INNER JOIN (PartNumbers INNER JOIN Components ON PartNumbers.PartID = Components.partID) ON SRU.SRUID = Components.SRUID
WHERE PartNumbers.PartID = [lst_PN].[Value]
ORDER BY 4, 5;

BTW, where is [lst_PN].[Value] coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FoxProProgrammer]

rac2: Good idea to use two steps to resolve this issue. I would have tried it, except PHV came up with a one step approach. You raised a good point about whether DISTINCT is required because of a problem with the way the tables are Joined. A summary of the structure of my database follows. Please feel free to let me know if there's a better way to structure my tables. I only included the pertinent fields from the tables in question.

Table [PartNumbers]
Fields:
PartID (Primary key, AutoNumber)
Part Number (Text)
...

Table [SRU]
Fields:
SRUID (Primary key, AutoNumber)
SRU (Text - the name of a circuit board)
...

Table [Components]
Fields:
PartID (foreign key to PartNumbers.PartID)
SRUID (foreign key to SRU.SRUID)
Component (Text - refers to a reference designator on a schematic, e.g. U1, R2, C3.
...

Each record in Components contains a foreign key to its part number (PartNumbers.PartID) and circuit board that it is used on (SRU.SRUID).

There is a one to many relationship between PartNumbers.PartID and Components.PartID. Many components can have the same part number, but part numbers are unique in the PartNumbers table. This occurs because the same part number can be used on multiple circuit boards, and even on the same circuit board. For example, U1 and U4 on circuit board A, and U9 on circuit board B might all have the same part number.

There is a one to many relationship between SRU.SRUID and Components.SRUID. This occurs because there are many components on each circuit board.

Now, my application includes a form that displays all [PartNumbers].[Part Numbers] in a list box. The name of the list box is lst_PN (this answers PHV's question). Another list box on the form displays the circuit boards (SRU.SRU) where the part selected in lst_PN is used. Since the same part can be used more than once on the same circuit board, the query that populates lst_SRU contains duplicates unless I use the DISTINCT clause.

If the ORDER BY is set to SRU, Access sorts "ABC 10" before "ABC 2". I want the SRUs to be displayed in the list box in the order "ABC 2", then "ABC 10". That's what I was trying to do with the ORDER BY clause. It works as long as the DISTINCT clause is removed, and I even use it in another list box that is populated with a Query of all SRU.SRU.

So, the question is whether I should use the DISTINCT clause or structure my tables differently. It seems to me that this is one of those cases where the DISTINCT clause is required, but I'm definitely open to suggestions.

PHV: Thanks for the very clever idea. Your ideas never cease to amaze me! lol It works, but I must admit that I don't understand what ORDER BY 4, 5 does. Doesn't the ORDER BY clause have to contain a string? Does 4, 5 refer to the columns in the Query results, or something else?

Thanks,


dz

 

[PHV]

Does 4, 5 refer to the columns in the Query results
Exactly.
[pedantic]the ordinal position[/pedantic]

BTW, what is the PrimaryKey of Components ?
Shouldn't it be (PartID,SRUID) ?
But, if it were, you shouldn't have duplicates issue with the above query, so I wonder ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FoxProProgrammer]

Reference the ORDER BY... I didn't know that you could set the Order to a column number in the output. There are a lot of things that I don't know, which is one reason that I sincerely appreciate all your help, and from others on this website! Just to make sure that I understand this...

I would guess that:

SELECT fld1, fld2 FROM tbl ORDER BY fld2

would return exactly the same records and sort as:

SELECT fld1, fld2 FROM tlb ORDER BY 2

Now for your next ponder...


Components doesn't have a Primary Key. I didn't think it needed one because it isn't the Parent table for any Child tables. That said, if I did set up a Primary Key, I think it should be SRUID, Component, no? I don't see how to make PartID, SRUID the Primary Key because those fields are foreign keys in a One to Many relationship. By definition they will be duplicated in this table. Each Component/SRU combination has to be unique because the same circuit board can't have more than one component with the same name. For example, there can't be two components named R1 on the same circuit board. It's like saying that two separate resistors on the schematic have the same reference ID. I do check to make sure that the user doesn't duplicate Components on the same SRU when they enter data. If they enter a duplicate - Error and no save.

Are you suggesting that I structure my tables differently, and if so, how?

Thanks!

dz

 

[rac2]

Ah ha. Then I think the JOINs are OK and DISTINCT is a good solution.

You have to get up pretty early to top PHV, he is good.

Regarding the primary key, it can be a composite key formed from two or more columns. Taken together PartID and SRUID are in fact primary keys for the components table, the combination is unique. The columns may not be marked as such in the definition of the table, but they could be.

 

[FoxProProgrammer]

Thanks, rac...Just for clarification, did you mean that the combination of SRUID and Component are unique? PartID and SRUID do not form a unique combination. The reason is because multiple components on a circuit board can have the same part number. For example, if C1, C2, C3, and C4 all have the same part number on the same circuit board, there would be four records in [Components] as follows:

PartID SRUID Component
3 1 C1
3 1 C2
3 1 C3
3 1 C4

The combination of Component and SRUID has to be unique, which is why I think that Component/SRUID would be the Primary key if I created one. IOW, the same Component can not be used more than once on any particular SRUID. If there is a better way to structure my tables, I'm way open for suggestions.

BTW, the rumor going around is that PHV single handedly developed Access, VBA, SQL, Oracle, C++, C, Java, Fortran, Basic, and 68000 Assembly code, to name a few... lol

Thanks again for your feedback.



dz