Urgent help needed

[associates]

Hi,

I was wondering if anyone might be able to help me here. how do i stop the combobox to print multiple duplicate records?

for example,

in the combobox dropdown, there are records as follows
01
02
03
04
05
05
05
06
07

How do i ensure that it only displays the unique records only without printing 05 three times?

Your help is greatly appreciated.

Thank you very much

 

[Laurentech]

You have to use "Select Distinct" in the rowsource for the combo box.

Larry

 

[associates]

Thank you Larry for your speedy reply and support.

It doesn't work. It's me that didn't explain it well.

Here is the rowsource i got from the combobox

SELECT DISTINCT [Job Register].Job_ID, [Job Register].Industry_No, [Job Register].Client_No, [Job Register].Job_No, [Job Register].Job_Name FROM [Job Register] WHERE ((([Job Register].Industry_No)=[CB_Industry]) AND (([Job Register].Client_No)=[CB_Client])) ORDER BY [Job Register].Job_No;

I hope i don't confuse you. What happen is i have four little comboboxes. CB1 is for industry, CB2 is for client, CB3 is for job, and CB4 is for subjob. CB2 contains information depending on the CB1, CB3 depends on CB1 and CB2, and CB4 depends on all the other three CBs.

For example,
Industry
01 = commercial
02 = manufacturer

Client
01 = Hewlett Packard
02 = NEC

Job
01 = PC upgrades
02 = PC repairs

SubJob can be anything but
00 = (by default)
01 = blahblah
02 = blah blah

so if i've got records as follows
01010100
01020100
01020101
01020100

Now, when it gets to CB3, in the dropdown menu, i can see 01 appears 3 times because of the fact that there are 3 subjobs. That's my problem. How do i make it all appear only once. Is there a better way of doing this? SOrry i've been scratching my head for hours but still have no idea yet as to what way is the better way to go.

Your help is greatly appreciated.

Thank you very much

 

[xxyy]

Helo!

Have you tried to set 'Group by' the fields of the query the combo box bases on?

BR

Sz.Cs.

 

[associates]

Hi XXYY,

hmm.. I got error message as soon as i put in the group by as follows

SELECT [Job Register].Job_ID, [Job Register].Industry_No, [Job Register].Client_No, [Job Register].Job_No, [Job Register].Job_Name FROM [Job Register] WHERE ((([Job Register].Industry_No)=[CB_Industry]) AND (([Job Register].Client_No)=[CB_Client])) GROUP BY [Job Register].Job_No ORDER BY [Job Register].Job_No;

Maybe i put it wrong or because it's the job_id, industry_no and client_no i have near the SELECT.

Thank you in advance

 

[xxyy]

Sorry, I'm not good at SQL, I'm used to making queries in Query View.

But I made a test query and after examing its SQL code in SQL view it seems to me that NOT ONLY ONE FIELD follows the expression GROUP BY.

SELECT INVOICES.invoiceID, INVOICES.date_of_arrival
FROM INVOICES
GROUP BY INVOICES.invoiceID, INVOICES.date_of_arrival;

Maybe try to group the other fields too.

BR

Sz.Cs.