applying nested if statements of X = X-1 type logic

[dyonysus_v]

Hello,



I am relatively ok (I think) with MS Access but am getting used to 2010 and struggling with a couple of particular issues when trying to create my query. I'm sorry I can't send over the file as it contains sensitive / confidential info so I'll try to explain as best I can I am trying to pull data from essentially 3 tables (Contracts for GW, Contracts for Purchase Orders, & Timesheet Submissions). I have set primary key on Client, Comment, Contract No, Product Code, & PO_Days Purchased. There should be no duplicates of master contract related info in either the Contract or GW Contract tables (1:1 ratio), however the timesheet data table only contains the ID (Autonumber) field as the primary key as there will be MULTIPLE instances of these values being posted over time (ie against a specific date field - which may encounter multiple clients/contracts on the same date but there should never be multiple instances of the primary key fields described above as the PO Days Purchased field doesn't exist in the timesheet table). Think of it like "Contracts tables" = CREDIT, "Timesheet Table" = Debits.


On exporting the data to excel I noticed that the PO days purchased value (mapped across from the 1:1 ratio relationship in the Contract table) is being replicated on each of the multiple lines where I'm grouping by the primary keys (from the contracts table) so it's incorrectly inflating the number of purchased days:

eg.

Client Comment Contract Role Resource Year Month PO Days Purchased Days Billed Days Unused

XXX SoW 4301 PS-INST Omar E 2012 February 1 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Tom E 2012 April 24 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Tom E 2012 February 24 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Omar E 2012 June 24 0.25 0.75 (Calculated field)

Whereas what I'd like to see is:

Client Comment Contract Role Resource Year Month PO Days Purchased Days Billed Days Unused

XXX SoW 4301 PS-INST Omar E 2012 February 1 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Tom E 2012 April 24 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Tom E 2012 February 0 0.25 0.75 (Calculated field)
XXX SoW 4301 PS-INST Omar E 2012 June 0 0.25 0.75 (Calculated field)

Exporting to Excel and applying a nested if statement based on the line prior works < =IF(AND(C2=C1,H2=H1),0,H2) > but isn't feasible as there are numerous other fields in the query which use the PO Days Purchased to calculate values, such as revenue generated, etc so it defeates the pbject to do this outside of the main caluclation in the Access query. What I need is the knowledge as to how to apply this logic into Access query or another means of acheiving the PO days Purchased dispalyed in the results of my query ONLY on the first line of 1:N ratio results found.

Any help you can shed on the subject would be hugely appreciated.


I have a similar conundrum on the same query where I can't seem to get the results of an IIF statement to sum 2 calculated fields in the same query but instead I either get nothing, or what appears to be a concatenation of the two resulting values such as 00.75 when attempting to perform Sum([PO_Days_Purchased]+[GW_Offered]) when PO Days Purchased value is 0 and GW Offered is 0.75 !!!

If you can help with either it would be hugely appreciated but the nested if statement is the challenge that's of the most important at the moment as it's massively infalting my pivot table days Purchased values!

Many thanks
Roslyn

[rpeare]

Can I ask a couple of questions. Is there a specific reason this needs to be done in a query and not on a form or report? What you are asking to do is going to use Domain Functions (probably a dlookup or dmax or combination of the two) which are incredibly resource heavy and should not be used in queries.

can you give an example of the data that's in each of your tables that's producing your sample result and the SQL statement that's producing it.

Also


Sum([PO_Days_Purchased]+[GW_Offered])

likely it's valuing your fields as strings so you can do one of two thigns:

Sum(cdbl([PO_Days_Purchased])+cdbl([GW_Offered]))

OR

Substitute the formula that's creating each field into your formula so it'd be something like

Sum((Formula1) + (formula2))

[dyonysus_v]

Can I ask a couple of questions. Is there a specific reason this needs to be done in a query and not on a form or report? What you are asking to do is going to use Domain Functions (probably a dlookup or dmax or combination of the two) which are incredibly resource heavy and should not be used in queries.

can you give an example of the data that's in each of your tables that's producing your sample result and the SQL statement that's producing it.

Also


Sum([PO_Days_Purchased]+[GW_Offered])

likely it's valuing your fields as strings so you can do one of two thigns:

Sum(cdbl([PO_Days_Purchased])+cdbl([GW_Offered]))

OR

Substitute the formula that's creating each field into your formula so it'd be something like

Sum((Formula1) + (formula2))

Hi,

Thanks for coming back to me. The only reason why I've done this in a query is because it's partly because it's what I'm used to and also because when I tried to do this on a report because of the number of columns displayed, half of them are omitted from the report design, some of the calculated fields weren't displayed, and I ended up with similar "rollup" factor hen I did manage to get it to work, plus it ends up losing half the data off the right hand side of the report/form. I thought it would be easier to build the basic query itself, then overlay with a report/form etc or more likely export into an excel spreadhseet so that I can pivot the info by client / resource by time & revenue. I must admit I've never really used the domain functions so wouldn't know where to start!?! but am happy to take some guidance if you're felling up to it?

I've attached the requested info so hopefully this should give you a better idea of what I'm working with. Many thanks again!

R

[rpeare]

Arg my whole reply just got wiped out

I'll tell you what, can you create a sample database that has the bare minimum data in it to replicate your problem then post that database. It's going to be a pain for me to reproduce something you already have working. Just make a copy of your database, take out any information that's not directly related to this problem (tables, forms, reports, queries) pare down the information in the tables to an example or two but alter any fields that are private (you've only blotted out one fields so just put in dummy values for that field as long as their are consistent so let's say you had PPPPP in your field you could replace that with TEST1 in all cases of your sample data where PPPPP appears).

Also, please convert the database to 2003 I do not have access to 2007 all the time and 2010 none of the time.

Lastly some advice:

1. Don't use any reserved words in your database names (you've used date, month, year for example) it's a very poor practice and will cause you a lot of grief in the long run so correct it now if you can.
2. Don't use special characters in your object names (you've used a pair of parentheses in a table name) also a bad practice and also will cause you no end of difficulty with programming

[dyonysus_v]

Hello again, Thanks for the advice - I've updated this in the table and query concerned - I'll update the others as I go through them again in the course of the coming days/weeks :-)

Following your comments I've have an idea - if I change all the client names then I could potentially send you a copy of the entire mdb as I think this is really the only restricted / sensitive information but I'd feel more comfortable if I emailed to you directly rather than posting it on here? Would that suit you?

[rpeare]

If you do try to upload your database I can not read 2010 files on any machine I have access to on a regular basis, 2003 is the best option for me so you'll have to convert your database. If you're using any new functionality with 2010 (multi value fields etc) you will not be able to do a conversion without eliminating those items from your database.

One other thing with regard to your database. You have a table that has a date, then extracts the week, day, month, and year that's a completely redundant (and not to be mean, but useless) table.

You can parse all that information out of any date using the functions:

datepart("d", [datefield]) - for day of month
datepart("m", [datefield]) - for month
datepart("yyyy", [datefield]) - for year

your week calculation could be a couple of different things depending on whether you're doing the week of the month or the week of the year.

[dyonysus_v]

Hi,

Thanks for the info (I'm self taught so have probably picked up alot of bad habits along the way and one of these days will see if I can get myself on a course to be taught properly but as it's not the main focus of my job I generally try and "get by".)

The reason I had created this table is that eventually (when all my queries are working and displaying the correct results) I'd like to introduce time periods into the queries via the parameter criteria so that I can then start understanding how much is billed/burnt per month, quarter, half year, annually etc for each of the key fields (client, contract, role, resource etc). According to your comments above, am I correct in thinking that I won't need this Calendar table and that it would be more performance efficient to use the formulas above in each query? I'd be grateful if you could explain in more detail ('fraid I'm a bit of a ludite ;-) ).

It seems that I am using some of the new functionality (although what I'm not sure what exactly!) and so can't save to a prior format, so I've stripped out the confidential info and attached the file in the 2010 format. If you or anyone else can help I really would appreciate it. The query to be used as an example is "a - BAF - Time n Revb - By Client (Incl GW)".

Thanks again

[dyonysus_v]

Please see file in 2010 format attached.

[rpeare]

I can't read a 2010 file. Please convert it to a prior version.

[dyonysus_v]

Hi, I'm sorry - I don't know how to. If I strip out those fields then I don't think the query will work as it impacts the calendar table and most of the timesheet table, which would then cause the query to fall over?

Is there another way?

Thanks again,