displaying all records but filtering certain fields

[Hanjo]

I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.

Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.

I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.

Does anyone have any idea how I can keep all records but simply display info relating to work we did in the 2013 period?

 

[CJ_London]

From what you have described, your tables are not normalised which makes this sort of thing difficult, but you would need a query along the following lines:

SELECT propertyid, propertyname, iif(nz(period)=2009,"",fld1) as fld1_2013, iif(nz(period)=2009,"",fld2) as fld2_2013... 
FROM tblProperties

 

[Hanjo]

maybe I should have provided more info.

the query only contains one table.

but the table is built from two 'make table' queries, and about 7 append queries that update these two created tables. - or something to that extent.

the company has multiple 'types' of work, that can be done per property, and every type of work gets assigned a year (either 2009 or 2013)

seeing that the 'types' are listed in a drop-down lookup field, queries always displayed properties twice, if two types of work have been done on the property. or more, if more work has been done on it. - my employers didn't like the layout and wanted me to display these 'types' next to each other so that there would be one record per property.

We managed to achieve this through the above mentioned steps. Now I have one record per property, but this record contains both 2009 and 2013 work. I only want the 2013 work displayed but whenever I create a criteria to filter it, it throws away the entire record.

Thanks for the response, but it seems Greek to me.

 

[jdraw]

Hanjo,

It seems you may have a database structure/design issue. Can you posst a zip file of your tables and relationships?

 

[CJ_London]

but it seems Greek to me

With respect, if you provide little information you can only expect a general response. What matters are facts - your table and field names, plus examples of the data 'before' and 'after' and a clear description of what you want to do between 'before' and 'after'.

you say this

the query only contains one table.

and this

that update these two created tables

which only confuses the issue

Rather than making this 'big' table you might be better simply to work from further down the line, but it can only be a suggestion

 

[Hanjo]

Fair enough.

Here's my situation, I have properties listed in a table named pmnproperties - I have the 'work' done on these properties in a table named newpropprocessinfo. - the relationship between these two tables = many records per property. Now as I briefly mentioned above, this created a situation where queries contained many rows per property (if many 'work' records existed for the property in question) - we wanted to display these many records in one row, and I finally managed it, I now have one table, where every property is listed, and every 'work' record of each property is displayed horizontally 'next to it'.

I built a query from this one table, it contains everything I require, every 'work' record we have on a property is given a date 2009 or 2013 based on which time period it is applicable. So you would have a property 'erf 1 of Cape Town' and next to it, types of work we've done, they are usually '78' (if a 78 application was lodged in the property) 'OBJ'(if an objection was lodged on the property) and a bunch of others, but I think two options are sufficient.

I have a '78 value' field, that contains an amount, say 'R1000' a '78 year' field that stipulates to what year this '78' is applicable (this field would contain the 2009 or 2013) then the exact same thing for the 'OBJ', I have an 'OBJ value' field that would contain an amount, say 'R1000', and 'OBJ year' which would contain either 2009 or 2013.

I tried to create a criteria for both 'year' fields, in order to only display the 'null' and '2013' values. This resulted is any property records containing 2009 related 'work', to be lost completely. Instead, I require the query to return all property records, but only display 2013 (or Null) relevant values. - I will try to provide a zip file tomorrow.

Thanks for all the responses thus far and sorry for my potentially poor communication of the situation, it's not something I regularly do.

 

[CJ_London]

What matters are facts - your table and field names, plus examples of the data 'before' and 'after' and a clear description

You are still describing - do it like this

table pmnproperties
PropertyID autonumber PK
PropertyName text
PropertyAddress text
etc...

table newpropprocessinfo
ProcessID autonumber PK
PropertyID long FK
WorkDate Date
WorkPeriod Integer
WorkType Text
etc ...

Then put some sample data together - TIP enclose in code tags (see # button in advanced editor) to preserve spacing

[B][U]table pmnproperties[/U][/B]
PropertyID  PropertyName....
1              propertyA
2              propertyB
3              propertyC
...
 
[B][U]table newpropprocessinfo[/U][/B]
ProcessID PropertyID WorkDate    Period  WorkType
1             1       01/01/2010 2009   Heating
2             1       06/10/2013 2013   Decorating
3             2       07/11/2012 2009   replace kitchen
4             1       08/03/2013 2013   heating
5             2       15/11/2013 2013   windows

Then the result you want

PropertyID  PropertyName    WorkDate    Period   WorkType
1              propertyA         06/10/2013 2013  Decorating
2              propertyA         08/03/2013 2013   Heating
3              PropertyC

 

[Hanjo]

Data(table):

replaces (the ID)
jobconf
bankref
buildingname
municname
erf
deedtown
portion
farmname
sectional_title
unit
size
suburb
yearstart
firstname
emailaddr
surname
accountnumber
pptypeormant
ppvalueormant value
pptype:78
ppvalue:78 value
ppyear:78 year
categories:78 categories
pptype:OBJ
ppyear:OBJ year
categories:OBJ categories
ppvalue:OBJ value
pptype:OBJ Result
categories:OBJ Result categories
ppvalue:OBJ Result value
ppyear:OBJ Result year
pptype:78 result
ppyear:78 result year
categories:78 Result categories
ppvalue:78 result value
pptype:GV
ppyear:GV year
categories:GV categories
ppvalue:GV value
pptype:SUPP
categories:SUPP categories
ppyear:SUPP year
ppvalue:SUPP value



So that's the one table I am building the query from. I have all fields(listed above) selected.

I'll try to upload an example of the the data, there seems to be some irrational size limitations of uploadable files though?

 

[jdraw]

Use a zip file.