Need help with Access Query

[leongtay]

Dear Experts,

As per attached is a Access Database in which i download the table from an Excel file.

I am looking to create a query with certain criteria but did not have any luck recently.

In the following paragraphs , i will explain by bullet points to give you guys an idea of what is required and whether it is technically possible on Access.

1) I will need the query to show <Short code> that are only unquie to Korea.
That means shortcode with a count of 1 belonging to Korea only.

2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.

3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.

I hope u guys can help me on this.

 

[JHB]

I am looking to create a query with certain criteria but did not have any luck recently.

Do you want 3 queries or only one query?

If only one query, the below will never happend for CB01406, then the count=2

2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.

What result do you expect here?

3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.

 

[leongtay]

Hi JHB,

Many thanks for replying to my posts as i really can't find any idea how to go about it.

I am looking to create one query for the 3 points that i have mentioned. The main problem i am facing is point number 3.

What i want over here is that, if the shortcode exists in both China and Korea. I want the whole line item to not be shown. For example in the case of shortcode CB47004 it has a total count of 3. The shortcode is being repeated twice in Korea and once in China.

As a result, since it is shown in China i do not want this item to appear at all.

So technically speaking the query should only show shortcodes existing in korea only. If the shortcode exists in both korea and china (i.e. CB47004) or China only (i.e. CBR1356 & FA40352), it shouldnt be shown at all.

 

[leongtay]

Dear experts,

I have attached a crosstab query to give a better idea of what i want.

As per attached in the database file , there is a crosstab query that i have done.

If possible, the only <Shortcodes> that should appear will be AE47611, CB01406 and CB06934. This is done after setting the filter for <China> to <Blank>. As a result, the shortcodes that appear should be thoes pertaining to Korea only (i.e. AE47611, CB01406, CB06934).

However, i will need the other fields to be include for these shortcode. Not too sure if i'll be able to create a link so that i can draw information from other fields for these range of shortcodes pertaining to korea only.

 

[JHB]

According to your first post "CB01406" should NOT be in the wanted result, because the Count=2.

1) I will need the query to show <Short code> that are only unquie to Korea.
That means shortcode with a count of 1 belonging to Korea only.

2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
However, i will need the other fields to be include for these shortcode.

It is not possible, because there are different information in the fields, (only one example here).

SAP Product code for CB01406 are: ​

CB01406/0015/P72
CB01406/SAMP

So which SAP Product code do you want in this case, (try to look at the data)?

 

[leongtay]

sorry for the confusion caused. Basically i will need shortcodes pertaining to Korea only (i.e. that means with a count of 1 or more). If there are duplicates involved , then its ok to show as multiple line items if it is technically impossible.

The same goes for china, that means, if i select China i will only like to see shortcodes pertaining to China only. Be it if it is only a count of 1 or more.

I will also like the query to be robust so that next time when i include a another country, say, Singapore. Then i can choose shortcodes pertaining only to Singapore , be it if it is a count of one or more.

 

[JHB]

Then it is easy with 2 queries.
1. Find all the <Shortcode> for all contries different from the choosen one.
2. Then find all the <Shortcode> which not have a match in the first query.
I've made an example in the attached database, run the query "Masterlist Without Matching AllOtherContries".

... then its ok to show as multiple line items if it is technically impossible.

It is technically possible, you have only to decide which information should be shown, when there are different information in the fields.

 

[leongtay]

Hi JHB,

Sorry for the late reply as i didnt come online the last few days.

Your query works perfect, a friend of mine has also got back to me on a query he written in SQL which also works.

But i guess yours is better cos if i add more countries to the <Masterlist> there shouldn't be any problem. Anyway for the sake of sharing, the query as written by my friend is as below.

First query to get crosstab

TRANSFORM Count(Masterlist.ID) AS CountOfID
SELECT Masterlist.[short code], Count(Masterlist.ID) AS [Total Of ID]
FROM Masterlist
GROUP BY Masterlist.[short code]
PIVOT Masterlist.Country;

2nd query to get the results for shortcodes belonging to one country only.

SELECT *
FROM Masterlist
WHERE (((Masterlist.[short code]) In (select Masterlist_Crosstab.[short code] from Masterlist_Crosstab
group by [short code] having count([China]) + count([Korea]) = 1
)));