access functionality

[Hanjo]

Hi again

just a quick and hopefully easily resolved question - does access have the ability to create a query where all records are present, all fields are present, yet some data within some fields aren't displayed?

 

[AlexHedley]

Do you have an example with sample data.?

 

[Mihail]

Yes. Yo must have a criteria to select those data.

 

[Hanjo]

I am trying to add a zip file, yet I get an error, it says invalid file? why would this be?

 

[Mihail]

I am trying to add a zip file, yet I get an error, it says invalid file? why would this be?

Who said that ?
How you create the file ?
Show us the entire name for the ZIP file (including the extension)

 

[Hanjo]

I used winzip, the file is named 'Database issues.zipx'

 

[Mihail]

Create a ZIP file, not a ZIPX

 

[Hanjo]

ah, thanks, seemed to have worked.

So now to explain what I want to do with it, there's one table in the ZIP file and one query, the query contains all the fields and all records (no filters or criteria)

I want to maintain the above, but you will see that each record has a few date/year fields linked to it, a few would be OBJ year, 78 year, Appeal year, etc.

I want all data from all dates other than 2009 to be displayed. however, when I build a criteria, It causes the query to throw away certain records, even if those records have both 2009 and 2013 data, meaning the 2013 data that I wanted to see, was lost.

 

[Mihail]

SELECT Data.ID, Data.jobconf, Data.bankref, Data.buildingname, Data.clientname, Data.municname, Data.erf, Data.deedtown, Data.portion, Data.farmname, Data.sectional_title, Data.unit, Data.size, Data.suburb, Data.yearstart, Data.firstname, Data.emailaddr, Data.surname, Data.accountnumber, Data.[pptypeormant], Data.[ppvalueormant value], Data.[pptype:78], Data.[ppvalue:78 value], Data.[categories:78 categories], Data.[pptype:OBJ], Data.[ppyear:OBJ year], IIf([ppyear:OBJ year]="2013",[ppyear:OBJ year],Null) AS Mihail, Data.[categories:OBJ categories], Data.[ppvalue:OBJ value], Data.[pptype:OBJ Result], Data.[categories:OBJ Result categories], Data.[ppvalue:OBJ Result value], Data.[ppyear:OBJ Result year], Data.[pptype:78 result], Data.[ppyear:78 result year], Data.[categories:78 Result categories], Data.[ppvalue:78 result value], Data.[pptype:GV], Data.[ppyear:GV year], Data.[categories:GV categories], Data.[ppvalue:GV value], Data.[pptype:SUPP], Data.[categories:SUPP categories], Data.[ppyear:SUPP year], Data.[ppvalue:SUPP value], Data.[pptype:Appeal], Data.[categories:Appeal categories], Data.[ppyear:Appeal year], Data.[ppvalue:Appeal value], Data.[pptype:Appeal result], Data.[categories:Appeal result categories], Data.[ppyear:Appeal result year], Data.[pptype:Appeal result value], Data.[pptype:Screening], Data.[ppyear:Screening year], Data.[categories:Screening categories], Data.[ppvalue:Screening value]
FROM Data;


Find the field named "Mihail" and compare with the field "[ppyear:OBJ year]"
What you have to do is to change the "Mihail" name and to uncheck the "show" checkbox in the field "[ppyear:OBJ year]"

Note please that your "DB" is far far FAR away from a DB.

 

[Hanjo]

I didn't intend to send my complete DB, because it has thousands of tables and hundreds of queries, it would never be small enough to post to this site, due to their size restrictions on files.

 

[Hanjo]

I think I should just clarify, I want or need to filter or set a criteria for every 'year' field. not just OBJ year.