Categorize Records By Tasking Number

[x85712]

I currently have a table with fields for "project name," tasking number and due date. Since we often issue multiple projects under a single tasking number, we have 400 different "project names" with about 70 different project numbers. What I am trying to accomplish is the following:


-generate a report where the "project names" are categorized by the tasking number, i.e., display the number "1" on the left side & all the projects associated with that (only displaying "1" once), followed by "2" and all its projects, etc.
-create a form whereby a user can select a tasking number from a combo box, and generate a report dynamically showing all projects associated with that tasking number. The combo box should only show each number once, right now it shows each number as many times as it appears in the main table, which for some can be 5 or 10 times.

Thanks in advance!

[jzwp11]

The report is easy enough to do. With respect to the combo box, you would create a query on the main table and include the tasking number and set the unique property of the query to yes. In terms of SQL text it would look like this

SELECT DISTINCT [tasking number]
FROM [table name]

By the way, do you only have 1 table? If a tasking number can have many related projects, that describes a one-to-many relationship, which by normalization rules, requires two related tables

tblTaskingNumbers
-pkTaskingNumberID primary key, autonumber
-TaskingNumber

tblProjects
-pkProjectID primary key, auotnumber
-fkTaskingNumberID foreign key to tblTaskingNumbers
-txtProjectName

[x85712]

Thanks for the reply. I've got the query/combo box part knocked out.
I originally created the main table as one table, but it's still easy enough to split it up. Once that is done, assuming I'm still using tblTaskingNumbers and tblProjects, what's the best way to display a report with all open projects, but only display the tasking number once?

JG

[jzwp11]

You would set up a query that brings the appropriate tables together and base the report on that query. Then in the report you would setup a grouping level on the tasking number and display that in the group header.