Alright, I was wondering if there was a way to tell a query to auto-number a field if the field is blank. For example...I have a 'Make-table Query' that makes the table "Major Projects". Now in this table there are fields with project numbers and Kin Numbers. (See example)

Project #            Kin Number              Amount

101                       2005                                 $100
102                       2005                                 $200
103                       2005                                 $300
104                       2006                                 $150
105                                                             $125
106                                                             $250

Now, I have a query that groups together 'Kin Num' and makes a total in a field called [TotalAmount], so for example, It shows...

 Project #             Kin Num            TotalAmount

             103                      2005                   $600
             104                      2006                   $150
Now is where the problem is...
             106                                              $375

As you can see it groups together all the ones without a kin num and totals them. Now, I have remedied this situation to a degree - I go in manually to the table and number all the projects without a kin, starting at 1, and pressing the down arrow until it runs into the ones with kin numbers. This works, but I was wondering if there was a way to have the query assign a number to this field if one isn't already there so that it has it's own unique kin num, that way all the ones without kin numbers don't get lumped together and totaled. Or is there a way to tell it to understand that all the ones that have the field [kin num] blank, they are projects by themselves and need to just be totaled by themselves. Thanks for your help anyone

Can you post the SQL you have now as the make table query?

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount]
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
ORDER BY [Major Projects Table (John)].[Kin Num];

This is from the query that grabs the data from 'Major Projects Table (John)' and groups it by Kin Num, before I run this query is when I have to go into the table and manually add Kin Num's to fields that don't have them so that when the above referenced query runs, it singles out the ones without Kin Num's and gives them a total instead of totaling all the ones without [Kin Num]. Let me know what you think.

Does the the unknown Kin number need to be a running sequential number?

If the unknown Kin number can be displayed as a zero (or any other constant for that matter)

Then you can try a union query:

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount]
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
ORDER BY [Major Projects Table (John)].[Kin Num]
HAVING [Major Projects Table (John)].[Kin Num] Is Not Null
UNION ALL
SELECT 0, [Total Construction Cost] AS [Total Amount]
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null

It can be displayed as a zero, that's fine, but when it goes to total the projects by [Kin Num], won't it total all the ones with a kin num '0', as one total? Then I'd be back where I started from. The reason I manually number each one, starting from 1,2,3,4 and so on is so each project is unique and has a kin, that way it totals up only that one project and not all the ones without kin's. [Kin Num] is basically a number that ties 2 or more projects together. Often times there are Sub-projects of Projects...The sub-project is called the "baby", where the main project is called the "Lead" or "Mother". There are some projects that have no "babies", and they are called stand-alone projects. They have no kin num.

The union query should take care of the sum problem.

Check out the HAVING and WHERE clauses.

The first part of the query will group by Kin number and sum the costs
for only those records that have a Kin number.

The second part of the union query does not sum the cost so you should have a
record for each cost with no Kin number

I tried inserting that code and it didn't work. Is the problem in the SELECT 0 part of the code? Just a guess.

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount]
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
ORDER BY [Major Projects Table (John)].[Kin Num]
HAVING [Major Projects Table (John)].[Kin Num] Is Not Null
UNION ALL
SELECT 0, [Total Construction Cost] AS [Total Amount]
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null

It says there is a syntax error in the ORDER BY Clause...

Try moving the ORDER BY clause to the end:

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount]
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
HAVING [Major Projects Table (John)].[Kin Num] Is Not Null
UNION ALL
SELECT 0, [Total Construction Cost] AS [Total Amount]
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null
ORDER BY [Major Projects Table (John)].[Kin Num]

Ok, we're almost there. The only problem now is my next query that grabs these totals by Kin Num does not have all the data, so I need to have a field such as [DES #] which is basically a unique identifier of the project. Here is the code I have so far and I am getting an error message saying "The number of columns in the two selected tables or queries of a union do not match".

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount], [Major Projects Table (John)].Des, [Major Projects Table (John)].showme
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num], [Major Projects Table (John)].Des, [Major Projects Table (John)].showme
HAVING ((([Major Projects Table (John)].[Kin Num]) Is Not Null) AND (([Major Projects Table (John)].showme)="L"))
UNION ALL
SELECT 0, [Total Construction Cost] AS [Total Amount]
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null
ORDER BY [Major Projects Table (John)].[Kin Num]

See where I added [Des] and [showme]. All showme does is narrow it down to the "lead" project and like I said before about [Des] being the Project #. The reason I need [Des] is to pull a bunch of other data in the next query that runs off this one. All my other tables use [Des] and can be linked to gather more info...

Remember that a union query is 2 separate queries that are run a one time.
So the second half of the query (after UNION ALL)
must have the same number of fileds as the first half (before UNION ALL)

Add the same 2 fields to the second part
as are in the first part of the query

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount], [Major Projects Table (John)].Des, [Major Projects Table (John)].showme
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num], [Major Projects Table (John)].Des, [Major Projects Table (John)].showme
HAVING ((([Major Projects Table (John)].[Kin Num]) Is Not Null) AND (([Major Projects Table (John)].showme)="L"))
UNION ALL
SELECT 0, [Total Construction Cost] AS [Total Amount],[Major Projects Table (John)].Des, [Major Projects Table (John)].showme
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null
ORDER BY [Major Projects Table (John)].[Kin Num]

Gotta go home for the day. Thanks for the help, we're getting somewhere. I'll get back with you tomorrow.

I have altered the code a little bit to grab the First Des # listed, this is my 'Lead' Des. But I'm still having trouble running it. Here is my code...

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount], First([Major Projects Table (John)].Des) AS FirstOfDes
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
HAVING [Major Projects Table (John)].[Kin Num] Is Not Null
UNION ALL
SELECT 0,[Total Construction Cost] AS [Total Amount], First([Major Projects Table (John)].Des) AS FirstOfDes
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null
ORDER BY [Major Projects Table (John)].[Kin Num]

I keep getting the error message "You tried to execute a query that does not include the specified expression 'Total Amount' as part of the aggregate function."

Any ideas?

The problem is that the second part of the ubion query is not a totals query.

Try this:

SELECT [Major Projects Table (John)].[Kin Num], Sum([Major Projects Table (John)].[Total Construction Cost]) AS [Total Amount], First([Major Projects Table (John)].Des) AS FirstOfDes
FROM [Major Projects Table (John)]
GROUP BY [Major Projects Table (John)].[Kin Num]
HAVING [Major Projects Table (John)].[Kin Num] Is Not Null
UNION ALL
SELECT 0,[Total Construction Cost], [Major Projects Table (John)].Des
FROM [Major Projects Table (John)]
WHERE [Major Projects Table (John)].[Kin Num] Is Null
ORDER BY [Major Projects Table (John)].[Kin Num]

I think I've got it. I really appreciate all your help. That WAS the problem...I have another post that is regarding the same project. If you could take a look at it, I would thank you much.

http://www.utteraccess.com/forums/showflat...6821&Forum=,All_Forums,&Words=&Searchpage=0&Limit=25&Main=606821&Search=true&where=&Name=69130&daterange=&newerval=&newertype=&olderval=&oldertype=#Post606821&bodyprev=

That is the link to my other post. Thanks man.