EasyQuery having hard time

[newbieaccess203]

Table Structure. To see more accurate table see the attachment: Region	CountryCode	CityCode	Material	RepNo	RepRegion
MIDSOUTH	106	458296	Iron	50002239	London Repository
MIDSOUTH	106	458296	Iron	50085661	Spain Repository
MIDSOUTH	106	608013	Iron	50002239	London Repository
MIDSOUTH	106	608013	Iron	50085661	Spain Repository
MIDSOUTH	106	950486	Iron	50002239	London Repository
MIDSOUTH	106	950486	Iron	50085661	Spain Repository
MIDSOUTH	111	458296	Iron	50002239	London Repository
MIDSOUTH	111	458296	Iron	50085661	Spain Repository

I am trying to write a query which will list the duplicate value of CountryCode and CityCode
SELECT *
FROM Table1 s2
INNER JOIN Table1 s2


ON (s2.CountryCode = s1.CountryCode AND
s2.CityCode = s1.CityCode )
WHERE s1.RepNo <> s2.RepNo ;

this query ask me question to answer the city and country code.
i do not want to enter the data.
I want a query which will give me the listing from the table

for example: from the attach table the result should be

MIDSOUTH 106 458296 Iron 50002239 London Repository
MIDSOUTH 106 458296 Iron 50085661 Spain Repository

and than there should be next button, click which will take me to next dups record so for eg. if i click next it should show me:
MIDSOUTH 106 608013 Iron 50002239 London Repository
MIDSOUTH 106 608013 Iron 50085661 Spain Repository

Clicking Next should show me:

MIDSOUTH 106 950486 Iron 50002239 London Repository
MIDSOUTH 106 950486 Iron 50085661 Spain Repository

Clicking Next should show me:

MIDSOUTH 111 458296 Iron 50002239 London Repository
MIDSOUTH 111 458296 Iron 50085661 Spain Repository

[ssanfu]

SELECT *
  FROM Table1 s2
  INNER JOIN Table1 s2
    ON (s2.CountryCode = s1.CountryCode AND
        s2.CityCode = s1.CityCode )
  WHERE s1.RepNo <> s2.RepNo ;

Is this a typo?
Shouldn't the first "Table1 s2" be "Table1 s1"??

[newbieaccess203]

sorry, yes that was a typo error it is s1.
Also is there a way to add combo box on the same form which will list this result to show for particular region ?, Here in the example I've just mention (given eg. ) of one region. But there are 25 regions. So for each region if i want to see the result. Is there a way i can list those region in combo box and select which region I want and it will show me only for those region?

[June7]

You want to apply filter criteria to a form? Review: http://www.allenbrowne.com/ser-62.html

[newbieaccess203]

Does anyone have answer to the original query i was looking for to display result i mention in first post ? i need to display result where records in two column have same data multiple times but different in 3rd colummn. For example from list of records I want to display result like this :

NOTE : If you notice 2nd and 3rd column has same value in more than one rows but 5th column has different value for those duplicates

for example: from the attach table the result should be

MIDSOUTH 106 458296 Iron 50002239 London Repository
MIDSOUTH 106 458296 Iron 50085661 Spain Repository

and then there should be next button, click which will take me to next dups record so for eg. if i click next it should show me:
MIDSOUTH 106 608013 Iron 50002239 London Repository
MIDSOUTH 106 608013 Iron 50085661 Spain Repository

Clicking Next should show me:

MIDSOUTH 106 950486 Iron 50002239 London Repository
MIDSOUTH 106 950486 Iron 50085661 Spain Repository

[June7]

Query looks good to me but really need to work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

So what is the real issue - the query works and you need to know how to display data in combobox? Sounds like a dependent (or cascading) combobox. Assuming form is bound to a master dataset of records with CityCode, combobox can have a RowSource with criteria that references the CityCode in the form's RecordSource, reference a textbox bound to CityCode:

SELECT ... FROM queryname WHERE [CityCode]=[tbxCity];

Then code behind the form OnCurrent event to requery the combobox when form navigates between records: Me.cbxCity.Requery

Navigate records with the intrinsic tools or build your own buttons.

[newbieaccess203]

Again,

If you notice I am not trying to pull the records based on condition where countrycode=citycode. I am trying to pull the records where there are multiple records with same countrycode and city code but have different Repno. See the original table. Query I build pop up text box to enter data. I just need to simply display records from this table.

[June7]

Then perhaps the combobox is dependent on multiple criteria.

If you want combobox to display limited set, that limitation must be based on some filter criteria. So how do you want to provide the filter parameters? I suggested the combobox be dependent on record of form. You could also have user input parameters into UNBOUND controls.

[newbieaccess203]

I am sorry but I think you are mixing two different things

1) First Question I asked in very first post where I just need to write simple query from very simple table where few columns(countrycode and citycode) which are duplicates (more than one exactly the same) should display if another column (Repno) is different for those same records.

2) Second Question I later ask if I can pull those records for specific region by putting combo box and select the region from that box.

thanks

[June7]

1. The query you posted looks good, but as stated, would need to do some testing with your data

2. If you just want combobox to display all regions as a list to choose from and apply that choice to the dataset of step 1, review http://www.allenbrowne.com/ser-62.html

[newbieaccess203]

let me see if i can get you actual data and database and actual query i am trying to solve