Newbie needing a bit of help with queries (1 Viewer)

[MonkeyBoy]

Hello,

I'm a complete newbie, I've been teaching myself how to use access 2003 and write queries for a couple of months now but i'm looking for a bit of help with a task i've been set and IIf statements?

I've been asked to 'create a report' that shows all the products issued by the department over the last 3 calender years by day, week and month as well as identifying the team the officers responsible for issuing the product was working in at the time of issue. They would also like these results presented as a graph/chart.

Now, i've managed create a query that will pull out all the data needed ie

Issuedate showing the date the product was issued (the day),
productdesc showing the type of product issued,
officerdet showing and the officer responsible for issuing the product.

I've formatted the issuedate in another 2 columns to show the dates by week and month the product was issued.
So far so good however, i still need to identify the team the issuing officer was working in and this depends on the product issued. For example a rotovator issued by JSWoods would have been done while working in the land team while a Mop issued by SBrown would be done as part of the Utilities team. There are 24 teams in all and i created a nested IIf statement for these however, an error message came up saying it was too complex but i am at a loss as to how else i can do the iif statements.
Can i do separate iif statements using & or is there a simpler way?

thanks

 

[Beetle]

Do you have a table for Teams, and if so is that table related to your Products table in any way?

It all starts with the data structure, so we need to know more in order to help you.

 

[MonkeyBoy]

Hi beetle,

thanks for answering my cry for help. There is no table for teams, only issuedate, productdesc and officerdet. i was going to do an if statement in excel to show the team the officer belonged to.

I'm wondering if i could do some sort of grouping in my report - is that a simple thing to do?

 

[Beetle]

I would not recommend using an IIf statEment, or a Select Case statement, or any hard coded method for this. The problem with defining lists as hard coded values in an expression or in code is that if you ever need to add any values to the list, or the names of some of the values change, etc. then you've got to go back and re-write all your expressions / code, which is a major pain in the a**.

You should have a table for the Teams. Ideally, this table would have some type of relationship with your products so that you can determine which teams are responsible for which products. Most likely, based on the little I know so far, you have a many-to-many relationship here so you would need a junction table. Even if you don't establish a relationship, it's still better to have the values in a table so you can at least use a domain function like DLookup to return the desired value without having to hard code it.