How can I have customised table (and forms?) displays

[summerleas]

I use FileMaker Pro, but I might need to make 3 data bases available to others, so using Access could be useful. The data bases are simple tables with no relationships. So selecting/searching are sort of easy. But I have a couple of questions due to ignorance of Access:



First, if I use the table view of the data and want to select records, Access wants me to choose between searches based on the selected field in the selected record, but I don’t want to search through the records to find one with the right information in the field and use that. Can Access allow me to set up search criterion not based on the selection?

Second, FileMaker separates layouts (lists, forms etc) from the data base. So I can create a “table” view that only contains selected fields and scroll through and search that. I can also have a forms “list” view where there are several forms on the screen and I can scroll through and search them easily seeing several at once. Can I create such “views” in Access? Either or both. (I think Access uses the word “view” in a confusing way, because it seems to refer to selecting/searching. I mean a display on a screen.)

A simple example. A data base consists of about 27,000 names, locations, dates (years and months separately because of the source data, which is 19th century hand-written sales books), number of items etc. I need a display that includes only names and locations. I also need a display that includes only names and another field. Extremely easy to do in FileMaker Pro, but I have no idea how to do it in Access!

[isladogs]

You would use queries

[ranman256]

Very easy in access,
Add the field to the query.

[summerleas]

You would use queries

Succinct! Thank you.

A curiosity from my attempt to create a useful data base. I have two fields "Number" (an integer) and "Exchange" (text either blank or "Exchange"). So I created a calculation field "NumberExchanged" with IIf([Exchange]<>"",[Number],0) which works fine. So I created a calculation field "NumberNew" with IIf([Exchange]="",[Number],0) which doesn't work (returning all zeros). So I tried IIf([Exchange]<>"Exchange",[Number],0) and that doesn't work either. In the end I used IIf([NumberExchanged]=0,[Number],0) which works!

Why don't the other two work?

[isladogs]

Hi

First of all, I would recommend you don't use Number as a field name as its a datatype so is a 'reserved word' in Access.
Similarly to avoid confusion I wouldn't name a field Exchange if the data will often be that.

There is a difference between null values (never populated fields) and empty strings "" (AKA zero length strings or ZLS)
If you populate the text field then manually delete the entry its a ZLS

So IIf([Exchange]="",[Number],0) only gave zeroes as you hadn't populated the empty fields
The easiest way to manage this is using the Nz (null to zero) function where you assign another value e.g. "" where its a null

e.g. IIf(Nz([Exchange],"")="",[Number],0) will work correctly

So will IIf(Nz([Exchange],"")<>"Exchange",[Number],0)

As for IIf([NumberExchanged]=0,[Number],0):
If you create a calculated fieldin a query which depends on another calculated field in the SAME query, the results may not always work reliably (though you say they did here)
The reason is Access won't always process the first calculation before the second.
The safest methods to ensure success are
a) use the full calculation each time e.g.IIf(IIf([Exchange]<>"",[Number],0)=0,[Number],0) but nested IIfs can get very difficult to read
b) use separate queries for each to ensure the first calculation is done before the second

Not so succinct this time!

[summerleas]

No luck with your suggestions.

IIf(Nz([Exchange],"")="",[SaleNumber],0) fails with the message "The exression IIf(Nz([Exchange],"")="",[SaleNumber],0) cannot be used in a calculated column." (I changed Number to SaleNumber.)

IIf(Nz([Exchange],"")<>"Exchange”,[SaleNumber],0) also fails with the same message.

I googled Nz and found mention of variants, but googling variant did not explain the term! Is this a red herring or relevant?

Why does IIf([Exchange]<>"Exchange",[Number],0) fail? I am not referring to a null value or an empty string.

[isladogs]

No luck with your suggestions.

IIf(Nz([Exchange],"")="",[SaleNumber],0) fails with the message "The exression IIf(Nz([Exchange],"")="",[SaleNumber],0) cannot be used in a calculated column." (I changed Number to SaleNumber.)

IIf(Nz([Exchange],"")<>"Exchange”,[SaleNumber],0) also fails with the same message.

I googled Nz and found mention of variants, but googling variant did not explain the term! Is this a red herring or relevant?

Why does IIf([Exchange]<>"Exchange",[Number],0) fail? I am not referring to a null value or an empty string.

I tested each of the suggestions here as calculated fields in a query before posting. They worked for me.

id	NumberField	TextField
1	1	Exchange
2	3
3	23
4	7	Exchange
5	4
6	5

NumberField	TextField	NumberExchanged	NumberNew	ThisOneFails
1	Exchange	1	0	0
3		0	3	0
23		0	23	0
7	Exchange	7	0	0
4		0	4	0
5		0	5	0

SELECT tblSummerleas.NumberField, tblSummerleas.TextField, IIf([TextField]<>"",[NumberField],0) AS NumberExchanged, IIf(Nz([TextField],"")="",[NumberField],0) AS NumberNew, IIf([TextField]<>"Exchange",[NumberField],0) AS ThisOneFails
FROM tblSummerleas;

Test db attached

When a variable is not properly defined e.g. as an integer number or a string, Access uses the variant type instead - its a 'catch all' that can be anything ... but in normal circumstances its better to define properly. Its a red herring here.

IIf([Exchange]<>"Exchange",[Number],0) should only fail if some values are nulls/ZLS. Nulls are not equal to anything - not even another null - so the expression fails
Test it for yourself by populating the empty records with any text

HTH
Just logging off now as its very late here in the UK

[summerleas]

I thought you wrote "Just jogging off now ..."!!

The difference is that I added NumberExchanged and NumberNew as calculation fields in the table. In the table IIf(Nz([TextField],"")="",[NumberField],0) fails as I described, but IIf([TextField]<>"",[NumberField],0) works in the table, so IIf([TextField]<>"",0,[NumberField]) works.

Don't know why Nz fails in the table and works in the query.

[isladogs]

LOL. Just in your mind

You haven't said how you were trying to enter the calculated values in your table - update query? default value?
Anyway, it is generally bad practice to store calculated values in tables as these values may not stay up to date.
Use queries to do calculations on 'demand'

Not sure why you reposted the sample database with the calculated fields added

[ssanfu]

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables. For reasons, see http://allenbrowne.com/casu-14.html
Do not begin object names with a number.

Also see http://allenbrowne.com/casu-14.html

[CJ_London]

The difference is that I added NumberExchanged and NumberNew as calculation fields in the table.

Don't, they are very limited in scope (as you are finding out) and have performance issues if required to be searched or sorted (no indexing). The functions available in a table calculated field do not include nz for example. To see what can be used, use the expression builder, click on functions>All

@Steve - sorry, see you've already suggested this!

[summerleas]

Anyway, it is generally bad practice to store calculated values in tables as these values may not stay up to date.
Use queries to do calculations on 'demand'
Not sure why you reposted the sample database with the calculated fields added

OK, so the Access application is poorly designed and has an unreliable feature in tables. As in Excel, it is easy for an application to update calculations in the correct order, so I don't know why this error is in Access (it is easy to place calculations anywhere in FileMaker and have them update reliably).

Using queries to create different layouts of table data is clumsy and misleading, because I have to use queries when no search/selection is taking place. Also important is that the different views are apparently independent. That is a good feature, because alternative views are possible, but it is also a bad feature because if I switch to the table or a form then the selected record is not shown. In Filemaker I frequently find a subset of the records, select one and then go to a different layout (where the same record is selected) to view other fields.

I "reposted" because you initially (not surprisingly) thought I was doing something else (with queries) and I wanted to avoid any ambiguity.

[CJ_London]

Many years ago l tried to learn FileMaker but I found the design logic was so far away from most rdbms confusing I abandoned it - tho I have a friend who still uses it. I guess you are having the same problem going the other way. Many developers who come from an excel background have the same issue. But users of sql server, MySQL, oracle etc do not.

The intention with access is for users to interact with the data via forms and reports using queries. Access is not unreliable if used as intended

[isladogs]

Ajax put it more succinctly than I would but I agree with every word
I only used Filemaker for a short time about 20 years ago but found its flat file nature too restricting
For years, I used Excel to create pseudo databases.
When I first moved over to Access I founds it very awkward because I was trying to use it like a spreadsheet.
The two products are not the same and to use either to best advantage, you need to use them in the way they are intended.

Ultimately, Access is FAR more powerful than either Excel or Filemaker.
However for complex calculations, Excel is often better.
For simple flat file databases, perhaps the Filemaker approach is more straightforward - I can't remember.

[summerleas]

Ajax put it more succinctly than I would but I agree with every word
I only used Filemaker for a short time about 20 years ago but found its flat file nature too restricting ...
Ultimately, Access is FAR more powerful than either Excel or Filemaker.

I cannot remember FileMaker in the 1990s, too old. I use the version released in 2010. It is a fully functional relational data base with extensive calculation and scripting facilities. Far from flat. I haven't used Access enough to comment, but my impression so far is that it is probably the same in ability as FileMaker. Perhaps biased because I much prefer the FileMaker interface to that of Access.