Regarding duplicate records in a query

[June7]

Why did Query2 have only the MOFixed field? Need to include all fields in the query.

An aggregate query with grouping on the 3 key fields reveals that 2068 combinations have 4 records each.

SELECT Query2.EXCHID, Query2.MOfixed, Query2.CELL, Count(TX.EXCHID) AS CountOfEXCHID


FROM Query2 INNER JOIN TX ON (Query2.EXCHID = TX.EXCHID) AND (Query2.MOfixed = TX.MO) AND (Query2.CELL = TX.CELL)
GROUP BY Query2.EXCHID, Query2.MOfixed, Query2.CELL
HAVING (((Count(TX.EXCHID))>1));

You either need another key field or eliminate records from one or both tables. Use the above query with TX table and Query2 to identify the duplicate records.

The following retrieves 0 records from Query2:
SELECT Query2.* FROM Query3 INNER JOIN Query2 ON (Query3.CELL = Query2.CELL) AND (Query3.MOfixed = Query2.MO) AND (Query3.EXCHID = Query2.EXCHID) ORDER BY Query2.EXCHID, Query2.MO, Query2.CELL;

The following retrieves 4136 records from TX table:
SELECT TX.* FROM Query3 INNER JOIN TX ON (Query3.CELL = TX.CELL) AND (Query3.MOfixed = TX.MO) AND (Query3.EXCHID = TX.EXCHID) ORDER BY TX.EXCHID, TX.MO, TX.CELL;

[ritimajain]

In Query2 MOfixed field is MANIPULATED So that it can be linked with MO field of TX table as u suggested. I m unable to get you . Please suggest me in some simpler way what should i do to get same no of records as in TX table. I need fileld EXCHID , CELL , BLSTATE , Mo FIXED From trx table or you can say from Query2 and field BAND Related to CELL field from TX table . PLEASE SUGGEST ME SQL FOR THIS.

[June7]

Yes, MOfixed is manipulated field but need other fields in the query as well, at least CELL and EXCHID but I recommend you include all of them.

Build the queries as suggested. Look at the records of the last query. You need to decide what to do about the 4,136 records before can proceed further. That's where the duplicate compound key combinations from TX table show up.

[ritimajain]

last QUERY retrieves 133392 records .
I tell you what i exactly want .I want to create a form which has 3 combo boxes one is showing exchid field, when user click on exchid combo box field it will show only cell field related to selected exchid , and when user selects cell field from another combo box, it will show related band in third combo box and when user selects band i want count in a text box how many cells have related band , related to selected cell in 2 nd combo Box and it will decrease count by 1 if field blstate has MBL .
Thats y i m looking for exact count of records as in TX table

[ritimajain]

I can make any field common in both tables as i made MO fixed to make records exact in number. Suggest me a way to make a 'BLSTATE', 'BLA ' common in BOTH 'TRX' and 'TX' So that i can link them. I need BLSATE AND BLA from trx table

[June7]

BLSTATE AND BLA don't have data for every record. That makes a poor key field. I tried STATE as part of the compound key and that eliminated about a dozen dup combinations.

What fields from both tables should be used to produce a unique compound key? The answer is not obvious to me. You know your data best. Which fields should be used? Use as many as it takes. This could be the longest compound key ever.

[ritimajain]

hi , i m extremely sorry for late reply , i was busy in figuring out and making records equal in both the tables as you said earlier. Now both tables have same no of records i.e 141484. I made a Query2 which has field MOfixed from TRX table which is linked with TX Table in Query3 and SQL for that is-

SELECT Query2.EXCHID, Query2.MOfixed, Query2.CELL, Query2.BLSTATE, TX.[BAND]
FROM Query2 INNER JOIN TX ON (Query2.EXCHID = TX.EXCHID) AND (Query2.MOfixed = TX.MO) AND (Query2.CELL = TX.CELL);
But now too Query 3 doesnot have same number of records as table TX or TRX or Query 2 have. I dont know where i m getting wrong .Please help..!

I have again uploaded my updated db on this link https://app.box.com/s/y0rbhx6ynqra9b3tgxbs .. Please have a look and help me.

[stmoong]

It is easier to understand by using an unbound form with the form's on load event, combo boxes' on click events, together with VBA and a few simple queries for your purpose.

Anyway, to get the results that you desired, you need to create this query that gives you the unique recordset for the fields that you designate as compound keys. Don't worry about record numbers not equal to the TX at this point.

I have saved this as Query4.
SELECT DISTINCT TX.EXCHID, TX.MO, TX.CELL, TX.CHGR, TX.[BAND] FROM TX;

Next, you make use of Query2 and Query4 like so.
SELECT Query2.EXCHID, Query2.MOfixed, Query2.CELL, Query2.BLSTATE, Query4.[BAND]
FROM Query2 LEFT JOIN Query4 ON (Query2.CHGR = Query4.CHGR) AND (Query2.CELL = Query4.CELL) AND (Query2.EXCHID = Query4.EXCHID) AND (Query2.MOfixed = Query4.MO);

You will the same number of records in TRX since the base table of Query2 is TRX. What you will notice is there would be duplicate records in the result, but you will get 141484 records.

[ritimajain]

hey thanxx a tonne man..! I need your little help more. I have form which has 3 comboboxes , in which user have to select exchid , then cell, and then band , i want only count of selected GSM Band weather it is GSM900 or GSM1800 band not count of all the bands related to selected CELL. AND count will get decreased by one in text box if BLSTATE is MBL . .. How can i do this ..? I m that close to it. Please help.I m uploading my updated db Please have a look on follwing link
https://app.box.com/s/wnql96ku9wszc4xmwji6

[stmoong]

You can write some VBA code to do that in the On Click or After Update event to update the text box.

[ritimajain]

I have already written VBA code for that. Please have look on my db on following link
https://app.box.com/s/wnql96ku9wszc4xmwji6
then you will be able to understand my problem more effectively. There is problem in Row source of my LISTBOX

[stmoong]

You can use a query to count the number of records for the selected EXCHID, CELL, BAND and BLSTATE not equals to MBL.

You can use the DAO to run queries in the VBA code and get the recordset of the query, which should be the number of records. Then, set the number into your text box.

Go google for DAO database, recordset in MSDN if you are not sure how to use them.

[ritimajain]

I am not getting it , that is very complicated for me as I am new to access .Tell me some simpler methods to change row source of my listbox.

[stmoong]

Well, I'm sorry, I don't know of another way to do it. Normally, I will just write VBA code to do such tasks.

Maybe other forum users may know of a simpler way.

[ritimajain]

can you write for me?
row source of my LISTbox is
SELECT Query1.EXCHID AS [SITE ID], Query1.CELL AS [CELL SITE], Query1.[BAND] AS [BAND], Query1.BLSTATE AS BLSTATE FROM Query1 WHERE (((Query1.[CELL]) Like Forms!Form1!Combo4 & "*")) ORDER BY Query1.[CELL];

but when i change Combo4 with Combo6 AND CELL with BAND WHERE clause IT STOP WORKING. There is something wrong in WHERE CLAUSE .. but i dont know what? PLEASE HELP