Help splitting one column into two 1

[suzieQuz]

I have a database that tracks jobs for a company, some in the proposal stage some already started.
I have a field that is called Status, with a drop down to choose either Proposal or Backlog.
The database tracks the Ending Balance, Cost, and Profit
along with some other things.

What I want to do is split the Status field
If the Status Field says Proposal, the Ending Balance should be entered into a new column that is called Proposal Outstanding Balance,

If the Status Field says Backlog, the Ending Balance should be entered into a new column called BacklogBalance

These two new columns would replace the Ending Balance column.

Anyone have any ideas

 

[scriverb]

After you have manually added these two fields to your table then use this SQL as an example on how to update the two new fields. After the update is successful delete the old field from the table.

UPDATE [red][i]yourtablename[/i][/red] as A SET A.[Proposal Outstanding Balance] = IIF(A.[Status]= "Proposal",A.[Ending Balance],0}, A.[BacklogBalance] = IIF(A.[Status]="Backlog",A.[Ending Balance],0);

Check over the names and fill in your table name and this should work as requested.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[lespaul]

you will also need to replace the } (curly bracket) at the end of the first line with a ) (paren)

Leslie

 

[scriverb]

Holy Cow!! Thanks Leslie I didn't see the typo. Curse that darned Shift key.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[suzieQuz]

Only problem is, the Ending Balance is a calculated field that is derived from a Query, so altering the table, I don't believe will work, because that field does not exist there.

Any other thoughts???

 

[scriverb]

Okay, I must have missed something here. Your last posting is confusing. Are we working from a form when you want the ending balance to be split? What are the tables and the structure and where are you getting the EndingBalance figure. Describe the process a little clearer please. I though you were trying to modify a table and split an existing field into two new fields. Sorry about that. But, let's start over.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[suzieQuz]

I am sorry about all the confusion
Here goes:
I have a form set up that consists of these fields:

Customer
Job#
JobDescription
Status drop down choice of Proposal or Backlog
Materials
Labor
TransAccRec
TransLostProjects

Then I set up query that gives me a Quote based on Materials + Labor
Ending Balance based on Quote + TransAccRec. +TransLostProj

I am trying to create a Summary Report which shows the Ending Balance for Proposals in one column and Backlogs in another column
This would make it easier to separate the Proposals from the Backlog Accts.

Right now the info is listed as I posted earlier
Hope this helps clarify things.
I appreciate your help

 

[scriverb]

Okay, I think I understand this situation now. Your table is okay as is. You have created a query that lists all the columns that you need for your report but the Ending Balance is all combined for both Proposals and Backlogs. Let's just modify your query by adding two new columns. I could use your SQL right now but I think I can make an example for you that will give you a nudge in the right direction. Here is an example of a query with two new columns that you need add to your query. See the red code for the two new columns:

Select A.*, (A.[Quote] + A.[TransAccRec] + A.[TransLostProj]) as [Ending Balance],[red] IIF(A.[Status]= "Proposal",(A.[Quote] + A.[TransAccRec] + A.[TransLostProj]),0} as [Proposal Outstanding Balance], IIF(A.[Status]="Backlog",(A.[Quote] + A.[TransAccRec] + A.[TransLostProj]),0) as [BacklogBalance] [/red]
FROM [blue][i]yourtablename[/i][/blue] as A;

Now just copy and paste the red code into your already existing select query. Make any slight field name changes that may be necessary due to communications errors here. Now you query can be used in your report and you have three field: [Ending Balance], [Proposal Outstanding Balance], and [BacklogBalance]. The [Ending Balance] will be the same value as one of the other two with the remaining one being set to zero(0).

Post back with the results of trying this.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[suzieQuz]

THATS'S IT!!!!!

I cannot thank you enough!!

 

[scriverb]

suzieQuz: Outstanding!!! Thanks for the Star.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]