report or query - help... 1

[valley8338]

I am designing a database to keep track of projects. One part of it I made a form that keeps track of who has the project. This table is project track. i have it liked to the project info table by the project ID (not the primary key, but it is an indexed field). This is a one to many relationship since the project goes back and forth between several people.

Anyways, now I want to make a report or query (I am not really sure, i keep trying either/both) that will print out where the project has been. I would prefer to be able to select the project and just have that info come out.

This is the format I am trying to get out (with what table the info comes from.

project name (projectinfo)
ProjectID(projecttrack/projectinfo)
Project Lead (project info)
Designer (projectinfo)
sendtoprinterby(projectinfo)

to (projecttrack)
date (projecttrack)
time (projecttrack)
initials (projecttrack)
comments (projecttrack)

Any help would be greatly appreciated. I have been working on this for so long, I am lucky I have hair left...

 

[allanon]

Make up some dummy data and post it along with what you think the output should look like. Then I can help you.

 

[valley8338]

Project Name: Index Book
ProjectID: 0207-01
Project Lead: John Doe
Designer: Jane Doe
Send to Print: 8/1/2002

TO DATE TIME INITIALS COMMENTS
Bill Smith 7/1/02 8:00 jd Editing
Joe Schmoe 7/2/02 9:45 bs cover design
Jill Doe 7/2/02 1:45 js layout

-----------------------

Each line under the to, date, etc. is its own record. I need to pull multiple records into the report, but only the records that match the projectID. Someone suggested I use a subreport, but no data comes into the subreport when I do that. I get the header with the book information, but the project tracking does not show up. I have the link master parent and child fields set to ProjectID, but nothing.

I thought I was close this afternoon when I used a query with a parameter, but the query pulled all of my records and overwrote the projectID field (in the query not the table).

Thanks so much for helping with this.

 

[rustynail1978]

i'm a little lost as to what you are saying with all this. if you could send me a copy of the database to look at i'd be more than glad to see if i can help. i do not need data in the tables although it would be nice. i can always just put data in myself. let me know if you want to send me a copy of the database.

 

[allanon]

Hmmm, this does not look difficult. I need to see your table layouts. Post the layouts and the query that you are running. I would think that you have at least two tables. A master and a child project table. If so I can't see this being difficult.

 

[danvlas]

I see it simple(but a may be wrong).
Include the two tables in a query.
Join the tables (drag the field ProjectID from ProjectInfo over ProjectID from ProjectTrack).
Choose whatever fields you need
In the conditions row of ProjectID field type [Enter project ID]
Save the query.
Run the query to see the results.
In this way, you will only get info regarding projects that have been worked on. To get ALL projects, right click the join line in the query and select option 2 or 3, whichever results in an arrow pointing to ProjectTrack.

Create a report (try with a wizard first) based on the query.
Set a grouping level to ProjectID
After creating the report, move all 'fields' coming from ProjectInfo table to the group header
Leave all 'fields' coming from ProjectTrack table in the Detail section.
Re-arrange the text boxes to get the layout you need
Open the report.


HTH

Dan

 

[valley8338]

Thank you for helping me with this...

I tried what danvlas suggested. My queries are still coming up blank. When I used the projectinfo.projectid in the query and had the criteria set to [Enter Project ID], changed the join type so that the arrow pointed to project track, I was able to pull the project name and id in the query, but none of the project tracking information came up.

Here is a rundown of these 2 tables:

Project Info Table:
Number(primary key)
ProjectName
ProjectID
ProjectLead
Department
Contact
Extension
Purpose
RequestDate
DesiredDelivery
Delivery
Budget
requestquantity
mailing
mailcomment
sendtoprinterdate
designformat
designcomments
designer

PROJECT TRACK TABLE:
tracknumber(primary key)
to
date
time
initials
comments
projectID

The two tables are linked by ProjectID.
Projectinfo.projectid is an indexed (no duplicates) field.
projecttrack.projectid is an indexed (dups ok) field.

The current query I have has the following fields:
projectinfo.projectid
projectinfo.projectname
projectinfo.projectlead
projectinfo.designer
projectinfo.sendtoprinterdate
projecttrack.to
projecttrack.date
projecttrack.time
projecttrack.initials
projecttrack.comments

I have also previously tried to accomplish what I need with a subreport. When I do that, there is nothing showing in my subreport on the main report When I open the subreport, all the data is there.

Thanks for looking at this for me. I agree it doesn't seem like it should be anything hard, but I can't get it for some reason.

 

[jsolutions]

What are your data types for ProjectId in each table? Also, I noticed that your project ID contained a hyphen. To achieve that are you using an input mask, a format or is it actually being typed in to the field?

It sounds like you are doing everything correctly. So, I would back up and start from the basics. Create a simple query using the projectinfo table, call it qryProjectInfo. Include all the fields. Create a criteria parameter on Project Id as you did before. Run the query and enter one of the project Id's. Did you get data?

Now do the same thing with a different query based on the projecttrack table, call it qryProjectTrack. Run it and enter the same project Id as you did in the first query. Did you get data?

If both individual queries did not produce data for the same project ID and in your mind they should have (i.e. you think that the project ID exists in both tables) then it is potentially a data entry issue.

If both individual queries produced data, then try a third test. Create a new query (sorry for all the extra queries, but it will help you track the problem). In this new query, add qryProjectInfo and qryProjectTrack. Link the ProjectID field from qryProjectInfo to qryProjectTrack. Leave it as a type 1 join (the default) because if we have made it this far , then each query should contain at least one project id that is the same. Run this query. When prompted, enter the same project ID you entered earlier. This time you should get data.

I have turned on email notification for this thread. Try what I have suggested and let me know what you come up with and we will move forward from there.

-Jay

 

[valley8338]

ok, I ran the query for projectinfo and all the data came up. When I ran projecttrack, nothing came up. I double checked the table and there is data in the projectid field and it is what I am typing in and it matches what is in the project info table. i am at a complete loss...

 

[jsolutions]

Let me know the following :
What are your data types for ProjectId in each table? Also, I noticed that your project ID contained a hyphen. To achieve that are you using an input mask, a format or is it actually being typed in to the field?

 

[valley8338]

It is being typed in. The data type on both fields is text

 

[valley8338]

Wait. I just noticed that I have the projectid field in the project track as a lookup field. could this be affecting it?

 

[jsolutions]

Yes and No. No the lookup itself will not affect whether the data matches, but the source of the data must be typed in exactly as you have it in your project info table.

So, is the lookup field pulling from a table or did you type the values in to the lookup list?

 

[jsolutions]

Yes and No. No the lookup itself will not affect whether the data matches, but the source of the data must be typed in exactly as you have it in your project info table.

So, is the lookup field pulling from a table or did you type the values in to the lookup list?

 

[valley8338]

It is pulling from the projectid field of the project info table. I did that to be sure that the fields match

 

[allanon]

I recreated your tables and put in your test data and this worked:

SELECT ProjectInfo.ProjectID, ProjectInfo.ProjectName, ProjectInfo.ProjectLead, ProjectInfo.designer, ProjectInfo.sendtoprinterdate, ProjectTrack.to, ProjectTrack.date, ProjectTrack.time, ProjectTrack.initials, ProjectTrack.comments
FROM ProjectInfo LEFT JOIN ProjectTrack ON ProjectInfo.ProjectID = ProjectTrack.projectID
WHERE (((ProjectInfo.ProjectID)=[Enter A Project Id]));

Now I am not sure where you would be having a problem.

Post the actual query you are using.

 

[jsolutions]

OK - back to basics again. Open the project track table. Sort the table by the project id. Scan down and find the project id you have been working with. It is in the table right? (I've made this mistake before).

If it is in the table, ight click in the project Id field. In the filter for box type in the id of what you are looking for and press enter. Did it find it?

Let me know and we will work from there.

Jay

 

[valley8338]

jsolutions--

I got the following error when I tried to filter. "type mismatch in join expression"

both fields in the project track and the projectid table are set to text. The only differences int he field properties is that projecttrack.projectid is a combo box with a lookup and projectinfo.projectid is a text box.

 

[valley8338]

allanon--

I am not sure where I am supposed to type all of that in. I am working on access 97, not sure if that makes a difference. When I go in to create a query, there is nowhere for me to type any code.

 

[valley8338]

allanon--

I tried typing what you put in the expression builder. I received the following error when I tried to run it.

"The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parentheses"