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]

I am using ac97. When you go to create a new query, do not select any tables. on the upper left side you will see SQL with a down arrow. Click there. Should open the SQL area. This is where you enter that sql. Make sure you change the table names to reflect your own tables, etc..

 

[jsolutions]

Sounds like the lookup is bound to the first column of the lookup list. Did you use the wizard to build it??

Open the table in design view. Click on the ProjectId field. Down at the bottom in the properties section, click on the Lookup tab. In this tab, click on the Row Source. Click on the build button (the three dots out to the right). This will open what looks like a query. What fields are in the grid? I am guessing that there are 2 fields.

If there are 2 fields, the problem is that it is storing the primary key field as the data for the lookup field. Since the primary key field in a combo box is hidden by default, then your project ID is displayed. However, if you search, sort, filter or query on that field, it is looking at the stored value not the displayed value.

If all of this is true, the easiest work around is to make your project ID the primary key. As you stated earlier, you have it indexed with no dups. Therefore, it would work fine as a primary.

let me know.

Jay

 

[valley8338]

That is exactly what is happening. I noticed it earlier when I was trying something. I took out the primary key field and then everything started going haywire. I just changed it back.

Is there any way to make this work as is?
The only problem with making projectID my primary key is that it is not assigned until after the initial record is created.

Can I ask for project id but have the fields linked by the number(primary key)?

 

[nxwalk]

Is it possible to create a running sum of the group in a query. I've created a running sum in a report group section. But I can't find any technical support showing how to do this in a query.

i.e. if Norman has 5 records, I would like for the query to return the following result:
Name rNum
------ ---
Norman 1
Norman 2
Norman 3
Norman 4
Norman 5

I need to pull the previous years' salary for the employee. Using the DLookup funtion this can be done using the key field and "-1". However, a key must first exists in the query for reference in the DLookup statement. Please help. Thanks.

 

[jsolutions]

Can't think of a way to make it work. I am sure that it is possible, but the time it would take to think it all through is probably not a good use of time.

You said that the project Id is not assigned until the inital record is created. Where is this record being created? Is it in the project track table?

If so, you could have your look up link to the project info table. Then trap the on not in list event of the combo box. In the event handler, create some code to insert the new Project ID value into the project info table.

Does this sound feasible? It is much less work that trying to figure out the other solution.

Jay

 

[valley8338]

Here's how I have the records being created. I have a project request form. An end user types in what they need from us and submits. We then reopen the form and edit in projectid, project lead, designer, etc.

I think what I am going to try to do is have projectid autonumber and then go back and change it to the projectid that we need it to be. The format for the projectid has to do when the project is done yymm-##. This is how above me wants the projectid to look....

I'll let you know how it works out.
Thank you so much for all of your help!!!

 

[valley8338]

WOO HOO!!!!

It works!! I changed the projectID to my primary key (which is now causing me an entry problem, but anyways) Now I am able to pull just the project ID I want and see the tracking. Thank you so much for sticking it out with me!!!

 

[jsolutions]

Great - let me know if you have any other questions.

Jay