Query problem

[puppychew]

Hi - I'm a bit of a beginner and having a problem.

I have a database with many columns based on project number. I have created a 2nd project number field, removing the extension numbers (-1, -2).

PROJECT NO PROJECT NO2 AMT Status
a100-1 a100 $125 open
a100-2 a100 $ 50 open
c23 c23 $10 closed

I would like to create a query which would give me the following. Listing the project number once.

PROJECT NO2 AMT STATUS


a100 $175 open

There is no primary key. I tried to make PROJECT NO a primary key and got a message that it cannot be created because of duplicate values.

Help appreciated - thanks

[puppychew]

sorry columns did not post right

PROJECT NO----- PROJECT NO2------ AMT ---- Status
a100-1------------a100 -------------$125 ----open
a100-2 ---------- a100 -------------$ 50 ------open
c23 --------------c23 --------------$10 -------closed

I would like to create a query which would give me the following. Listing the project number once.

PROJECT NO2 -----AMT ----- STATUS
a100 ------------$175 -------open

[jbarrum]

This can easily be done but lets first address what may be a normalization problem. What is the difference between [PROJECT NO] & [PROJECT NO2]

It basically sounds like you want to sum [Amount] where ProjectNo2 is equal. Is that correct?

[puppychew]

project 2 just omits the -1, -2. I was able to make Project No a primary key. But need to sum data for each project by the Project 2 field which excludes the extension -1,-2.

I am able to group and add a sum for the sales amount.

The problem I am facing now is that I must use other fields to establish criteria, such as a date field. Project field A100 may be listed on 3 lines since there are 3 different dates. The query result then shows 3 lines not one like I need.

I would like to query all A100 that contain a date and an amount, and have a result of one line showing the project no and the sum amount. ( I know the date cannot be shown since there are more than one, but need it in the query).

Hope I explained myself ok.

thanks

[jbarrum]

do you want your query to have only (one) line per Project or do you want a line for each Project that also has the same date.

Antotherwords show me how you want the query to look for the following data:

Project

[jbarrum]

Project Date Amount
A100 1/15 100
A200 1/15 200
A100 2/15 100
A100 1/15 500

[puppychew]

yes, one line

Project Amount
A100 700
A200 200

[jbarrum]

Will the project number and sum be enough in the query.

[puppychew]

yes - that would be all I need to see in the result. The date I need in the query just to use as criteria. There are some dates that will be blank but will have funds that i need to exclude.

[jbarrum]

In that case use the query wizzard to build your query only using those two fields in your table and select the option for Summary (not deatil). This should get you what you want.

[puppychew]

I think I'm getting closer. I usually do a query in design view.

I also need to include the date field since I need to choose records that contain a date (is not null) in the query.

[jbarrum]

It will not work if you include the date field for obvious reasons.

[puppychew]

I thought there may be a way around it. So, the only way this would work is to do a query of a query etc. as I keep filtering information with a summary the last query. I had hoped there would be an easier way.

[jbarrum]

not that I am aware of but I am unclear as to what you want to do witt the date? Does it have anything to do with your query?

[puppychew]

If a project does not have a date, it should not be included - it was not paid.