Access Union Query

Hello,

The following screen shot is my query.

access query
Usually, only one set of criteria is true and everything runs fine, but now I'm running into where both criteria's are true.  When both are true, the result is:

query result
When this happens, I want it to only return the first row.

Thank you,
Joel
Genius123Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Add a second criteria in column BOM_Produkt, on the line labeled "or:"

<=8000 Or >=8200

Open in new window

/gustav
0
 
Dale FyeCommented:
That is not a Union Query, that is an INNER JOIN.

You could try:

SELECT Top 1 ...
0
 
Pawan KumarDatabase ExpertCommented:
So if you have multiple Ids the TOP 1 will not work... basically for each ID,POS_NR....we need first record.
We can do something like below.

SELECT ID,POS_NR, MAX(Shape) AS Shape , MAX(Aris) AS Aris , MAX(BOM_PRODI) BOM_PRODI
FROM
(
   /* Your EXISTING Query*/
) AS R
GROUP BY ID,POS_NR
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dale FyeCommented:
Except

Max(Shape), Max(Aris), Max(BOM_Prodi)

does not guarantee that you will get the Shape, Aris, and BOM_Prodi from the same record.  In this example, it would return:

Shape #1                999901          Aris Edges 2/4

Where Shape #1 comes from first record, and 999901 comes from the 2nd.

Since the query criteria specifices the ID and POS_NR, there is no need to group by those columns.  However, you would need to figure out what your criteria is for selecting the "Top" row, and add an Order By clause to the query so that the row you want would sort to the top.  It might look like:

ORDER BY IIF([BOM_PRODUKT] > 8000 AND [BOM_PRODUKT] < 8200, 1, 2)
0
 
Pawan KumarDatabase ExpertCommented:
I think we some unique ID or Date column to fetch the first record.
0
 
Dale FyeCommented:
Since the OP never responded back, there is no way to determine which of the provided solutions would have met the desired outcome.  

Dale Fye and Gustav Brock both provided solutions which might meet the OPs needs.

I recommend either splitting the points of deleting the question for not enough information.
0
 
Gustav BrockCIOCommented:
Solution provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.