Set maximum value for specific line

[calle21]

Hello,




First of all - I have just started working with MS Access and have very little knowledge. Looking at the image below, is there any simple way to set a maximum numerical value (for example 50) for the line highlighted in red? The limitation should only apply to specifically that one line.

[mike60smart]

Hi and welcome to the forum

When you say the line highlighted in Red, do you mean just the Field named "A" ?

[Micron]

I have just started working with MS Access and have very little knowledge

I suggest you read up on these topics}
https://www.accessforums.net/showthr...773#post521773

We often remark that described designs are like spreadsheets, which is a big no-no for db tables. That is the most spreadsheet-like design I've ever seen for a table, replete with Excel column labels as field names. If that is the case, stop what you're doing and read up in those topics and save yourself some grief going forward.

Since you implied that the max is for the whole record (row) the only 2 ways would be:
- to impose the same table field value constraint on every field from A to H (makes the most sense) but would apply to every record, not just the one you've highlighted.
- vba code, which makes far less sense but could be used to apply to individual records.

[calle21]

Hi and welcome to the forum

When you say the line highlighted in Red, do you mean just the Field named "A" ?

Well, the one beneath "A" that just says "30" right now.

Since you implied that the max is for the whole record (row) the only 2 ways would be:
- to impose the same table field value constraint on every field from A to H (makes the most sense) but would apply to every record, not just the one you've highlighted.
- vba code, which makes far less sense but could be used to apply to individual records.

I would need the latter then I guess? Because the max. value should ONLY apply to that one field, that is highlighted in red.

Unfortunately I can't really change anything about the spreadsheet design, as a spreadsheet similar to the one I screenshotted is part of a videogame. And I would like to implement that max. value so that the value assigned to certain people within that video game doesn't go any further than that.

[mike60smart]

Well as Micron has said you are thinking Excel and not Access.

Can you upload a screenshot of your Relationship Window?

[calle21]

[mike60smart]

Well you definately need to look up how tables are related .

All of your tables are just linked with No Referential Integrity Enforced.

You are also linking PK to PK which is a 1 to 1 Relationship, which is not the usual method.

It would normally be a PK linked to a FK in a 1 to many Relationship.

[Micron]

Because the max. value should ONLY apply to that one field, that is highlighted in red.

Sorry, still not clear. What Excel considers a column is a field in db tables. So your comment may mean also every row below 30 in "A1" (50,60,50,30,20,40...) also cannot exceed some value. Or to you, "field" might mean that one intersection (cell) of row/column (in db speak, record/field) which just happens to be 30...

Unfortunately I can't really change anything about the spreadsheet design, as a spreadsheet similar to the one I screenshotted is part of a videogame. And I would like to implement that max. value so that the value assigned to certain people within that video game doesn't go any further than that.

Such justifications are seldom really true and are the result of not knowing how to create something that is properly normalized but still works in the end. That type of design usually presents data input and retrieval issues on an on-going basis. If properly normalized that issue goes away. Then you present the data in a spreadsheet format by using crosstab queries. 'Nuff said by me on the topic.

[Welshgasman]

Best sticking to Excel perhaps?

[ano]

even i just left excell because it become too slow, even reread several times im not sure what you want.
but looking i would suggest select top 1 ... the logic i not found yet. there is something missing in your information.
perhaps upload your database zipped so we can see what you doing already

[madpiet]

The hard part about getting help on a question like yours is that without understanding what the columns mean, it's not really possible to know how to fix what you have. Generally speaking, though, anytime you have columns that end in a number, that's a sign of a repeating field.
I had that kind of thing in a job a long time ago where I was doing cancer research and the data I got had basically
PatientID, WeekNumber, [SymptomName], Causality1, Relatedness1, [SymptomName], Causality2, Relatedness2...

and the solution was to basically grab all the columns with data and append them so that I had

PatientID, WeekNumber, Symptom, Causality, Relatedness

We can help if you can explain what the various columns mean. Then someone can suggest a better design.

[ano]

the main issue here is you write about line and high light a field.
in before update you can put the condition and action you like as long there is a unique field u can pin on, but need in every field using sub routine will reduce ur writing
even in excell you have to explain what you like same is for access then we can help
the logic has to be clear to make the right suggestion

[CJ_London]

Suggest describe what you want to do with the columns (called fields) e.g. based on what you appear to be saying is

'I want that if WorkerUID=1 then field A has a maximum value of 45'

how are the values determined at the moment? some sort of calculation or accrued value? user entry?

provide other examples for the other fields and other WorkerUID's and how the maximum value is determined - is it always the same maximum? based in some way on the other fields or rows (called records), something else?

And what do you want to happen if the value exceeds the maximum? highlight it? limit it to the maximum? prevent a user entering a value that exceeds the maximum?

If you already have this in a spreadsheet - show the formulas you are using to populate the values and set the maximums

[madpiet]

Not totally sure what your tables mean, but if I see a column name/field name with a numeric suffix, I'm immediately wary. If you post your design and explain what the columns mean, I'm pretty sure the folks on here can help fix your design before you're neck deep in something that doesn't work very well.