Help ME Pls

[shimul]

Hi all,

I am new in access and visual basic. Please help me.

I have query where I am getting result fine, but it repeating value (Please see attachment).

However, I dont wanna repeat value.

Can you please give me idea how I can do it?

Thank you in advance.

Shimul

 

[irish634]

Try adding the word "DISTINCT" after "SELECT" in your query.

Ex... SELECT DISTINCT [MyField] FROM [MyTable]

This will only return the one value. Without seeing it in action, I'm not sure if it's what you are looking for or not.

 

[Balr14]

You will need to restructure your tables or do some "make table" queries to accomplish what you wish. Also, your counting is wrong or you wouldn't get 0 values.

 

[shimul]

Thank you.

Can you please give me idea about "make table" quries?

 

[Balr14]

After looking closer, "make table queries" are probably a bad idea. You can do it with a simple VB routine. This assumes a detail table (DtlTbl) of Desc (text,50) and Number (text,50) and summary table (SummTbl) of Desc(text, 50) ,Cnt(long integer) and Number (text,255). The detail table should contain your description and number values, the summary table should be defined, but empty:

Public Sub FillSumm()
Dim MyDB As DAO.Database
Dim DtlData As DAO.Recordset
Dim SummData As DAO.Recordset

Set MyDB = CurrentDb
Set DtlData = MyDB.OpenRecordset("DtlTbl")
Set SummData = MyDB.OpenRecordset("SummTbl")
DtlData.MoveFirst
SummData.AddNew
SummData!desc = DtlData!desc
SummData!Number = DtlData!Number
SummData!cnt = 1
DtlData.MoveNext

Do Until DtlData.EOF

If SummData!desc = DtlData!desc Then
SummData!Number = SummData!Number & "," & DtlData!Number
SummData!cnt = SummData!cnt + 1
Else
SummData.Update
SummData.AddNew
SummData!desc = DtlData!desc
SummData!Number = DtlData!Number
SummData!cnt = 1
End If

DtlData.MoveNext
Loop
SummData.Update
DtlData.Close
SummData.Close

Exit Sub
End Sub

You may want to play with how the Cnt is incremented; for example set it to zero and only add to it if Number is not null. Sorry if this is a little crude, I'm not a good VB coder; my strength is Assembler language.