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