Not even sure where to start...

[Johnny Drama]

so I'll start from the beginning. I have a table that is on the "many" side of a one to many relationship. One of the fields in this table is named "Control Number" and that field can contain the same control number in many different records. I'm trying to figure out a good way to pull this data into a form by a query and allow the users to filter based upon the control number. I was trying to use a combo box, but when I try that it will list the control number multiple times, ie. if Control1 is in 5 records the combo box will show Control1 5 times. Any ideas...and if I didn't make it clear feel free to tell me I'm speaking in gibberish.

 

[pbaldy]

Try a rowsource of

SELECT DISTINCT [Control Number]
FROM TableName

I would advise against the space in the name, if it's not too late.

 

[Johnny Drama]

Thanks...I'll give that a shot and let you know how it works out...and I can change the name...it's my dev copy of the db.

 

[Johnny Drama]

No dice...it just has an empty combo box when I try that.

 

[DCrake]

Try:

SELECT ControlNumber AS ControlNo, "(" & Count(1) & ")" AS Items FROM TblName GROUP BY ControlNumber HAVING (((ControlNumber ) Is Not Null)) ORDER BY ControlNumber;

Set you column count to 2
Column widths to 2cm,2cm
Set column heading to yes

This should give you a list of all the control numbers and the number of occurances found in thesecond column.

Don't forget to replace your field/table names from mine to yours.

David

 

[Johnny Drama]

Actually I got it to work using pbaldy's method. There was an issue with my query that caused it to come up blank...once I fixed that it worked perfectly.

Thanks to both of you for the help!

 

[Johnny Drama]

Okay....now that I've got that working I can't seem to get it to filter the records so only those associated with the control number are pulled. I looked at that tutorial on the datapig website but can't seem to make it work. Thoughts?

 

[pbaldy]

I'm not familiar with that tutorial; how are you trying to filter the records?

 

[RainLover]

While Paul gave you a solution that works, I don't believe that to be the correct method. But I stand to be corrected.

As you are using this this value several times it makes me feel that you have not Normalised your tables. These Values should be stored in a separate table and only stored once.

 

[Johnny Drama]

Ned, the value is only stored once in a table (tblControl) and then it is a foreign key in a second table (tblPBC) on the many side of a one-to-many relationship. The query the form is pulling the data from is the tblPBC table which is why it appears more than once.

Paul, the vid I watched was at http://datapigtechnologies.com/flashfiles/buildcustomfilter.html I've seen the link on this site in other posts about filtering...

 

[RainLover]

Johnny

That is good to hear you have things set up properly.

So that been the case why are you not pulling the value from that related table.

That is what I would do.

Regards