Queries and SQL in a form

[J]

I have a form that displays Project numbers at the top of the form and
displays information about that project number below. It is possible
that a Project Number can be there more than once on a form. My
problem is being able to get it so that all the information regarding a
particular Project Number appears all on one screen rather than
appearing randomly throughout the records. I was told that I could use
a query or an SQL statement to fix the problem and also something
called a UNION SELECT query. If anyone could explain how I would
implement these methods it would be greatly appreciated. I am also
using a subform if that would have anything to do with the solution.


For Example:


Right now my form displays like this:


A01
Information Here


A02(New Record)
Information Here


A01(New Record)
Information Here


But I want it to display like this:


A01
All information on A01 here


A02(new screen)
All information on A02 here


A03(new screen)
All information on A03 Here


etc

 

[BruceM]

Maybe I'm missing something, but it shouldn't be a problem to get all of the
information on one screen (assuming there is enough room). What do you mean
about a project number appearing more than once? Again, it shouldn't be a
problem if all you are doing is causing a field to display in several
places. The information does not appear randomly throughout the records,
but according to how you have set up the database. How is the project
number being assigned? Apparently it is not a unique value. I assume it is
supposed to be. It seems you have allowed duplicates in that field (table
design). What is the database structure? You say you have a subform. To
what record source is it bound? How does that record source relate to the
main form's record source?

 

[J]

The project number would be appearing in a table more than once but not
all in order. For example, A1 could be found in records 1 5 and 9
rather than being consecutive. I'm trying to get a complete record set
to display for each instance of the Project Number which is assigned by
a different person. The reason for the project number being there more
than once could be because of various parts to the project. The
subform that I have is connected to the table that has the information
to do with the Project Number but it only displays data for one record
at a time. However, I need to have it that when 1 project number
displays that all records for this number are displayed in the form. I
hope this gives you a better understanding of what I'm talking about.

 

[Brian Bastl]

How are your relevant tables set up?
tbl1 ProjectID, ProjectNum, ProjectStartDate,...?
tbl2 ProjDetailsID,ProjectID, ProjDetails,...?

if so, then you use tbl1 as the recordsource for the main form, and tbl2 as
the recordsource for your subform. Then set your Master/ Child links to
ProjectID(?). That will produce what you are describing.

HTH,
Brian