Yes/No Datatype (1 Viewer)

Winnipeger · Jan 10, 2020

Hi there,

Can you give me an example or lead me to a thread about changing the Yes/No datatype using a parameter/condition. I cant seem to find it anywhere

the problem goes like if the number of quantity is met, the NO should be changed to YES. Should it be done in a table, query or both?

thanks in advance.

The_Doc_Man · Jan 10, 2020

Not enough info to answer the "Table/Query" question.

The question will also depend on whether anything else is supposed to happen when that change occurs.

And I'm not sure why you want a Y/N action relating to a parameter.

The condition in VBA is easy enough if your value is part of a code segment:

Code:

 YNVariable = DCount(something, somewhere) > somenumber

In SQL (which can ONLY Update fields in a table/recordset, not variables in a code segment), you might do:

Code:

 UPDATE sometable SET YNField = somevalue > somenumber ;

But I'm not sure if I actually understood your question - or whether you were overthinking it somehow. There's not much to say or do.

arnelgp · Jan 10, 2020

yes use query to update the Yes/No field, eg

Code:

Update [I]table1[/I] Set Completed = True Where Nz(table1.Totals, 0) > 0 And
   (Nz(table1.Seq1, 0) + Nz(table1.Seq2, 0) + Nz(table1.Seq3) [I][ + Nz(table1.SeqN ][/I] = Nz(table1.Totals, 0))

plog · Jan 10, 2020

if the number of quantity is met, the NO should be changed to YES.

My guess is you don't need the field at all. If that Yes/No field is completely dependent on the numeric field then you should not have it.

You would remove the Yes/No field from the table and create a query that uses a calculated field to determine if the field shoul be Yes/No and then reference the query when you need that value instead of storing it.

jdraw · Jan 10, 2020

Winnipeger,

Perhaps you could provide a sample so that your requirement was very clear.
There is some confusion in your post that has responders "guessing" in their responses.
Clarity is key to getting focused advice.

Pat Hartman · Jan 10, 2020

Let us not equivocate on this answer.

I'm with plog on this one. If the count tells you the answer, you don't need the y/n field and keeping the y/n field would be redundant, a violation of normal forms, and potentially lead to errors should the count change and the y/n value not be updated in sync.

Just use the count when you need to know if there are items or not that meet your criteria.

Winnipeger · Jan 11, 2020

Thanks guys for all your inputs! and sorry for the confusion

being a newbie in access, i am really overwelmed with so much info.

Going back to my problem, I have attached a sample db of my project.
I just wanted to add two more columns in my JobOrderTracker query:

STATUS
Not Started = if Seq1 is <= 0
In Process = if Seq1 is > 0 but less than OrderQty
Finished = if Seq3 is equal to OrderQty

Done?(Yes/No Datatype)
NO = if Status is Not started or In Process
YES = if Status is finished

plog · Jan 11, 2020

First thing I would do is throw away your cross-tab query. Those things are special and should only be used to transform data values into column headings once all data manipulation is complete in their underlying data.

You still need to manipulate data:

STATUS
Not Started = if Seq1 is <= 0
In Process = if Seq1 is > 0 but less than OrderQty
Finished = if Seq3 is equal to OrderQty

Looking at that this is going to require an Iif statement. Here's a link to how those work in Access:

https://support.office.com/en-us/article/iif-function-32436ecf-c629-48a3-9900-647539c764e3

Unfortunately, your description of how it works doesn't line up with your database. Looking at your tables, you have no field called [Seq1], so using it in a comparison is impossible.

I suggest you give a better explanation of how you want [Status] to be calculated by using the fields you do have in your tables.

Winnipeger · Jan 13, 2020

Thanks for the response.

I got your point. It's just this is the only way I know to track the status deoending on the sequence selected thru lookup dropdown.
I can include Status field on the table however I have no idea how to meet the criteria since it depends on the sequence selected.

Anyone who can suggest a different approach to track by sequence?

plog · Jan 13, 2020

For us to help you properly structure your database we would have to know more about the real world system it models. My suggestion is to give us 2 explanations:

1. Pretend its career data at an elementeray school and describe what it is your organization does. No database jargon, no talk of fields nor tables, just tell us what it is you guys do.

2. With minimal database jargon, explain what it is the database is to help you accomplish. Tell us its purpose within the context of #1 above.

Winnipeger · Jan 13, 2020

I am in a manufacturing company who deals with train rail plates. What we do is we machine plates according to customer needs/orders.

Basically, for every job order there is sequence such as cutting, then milling,then inspection (some plates does have more than 3 sequence).
With this, i just want a system that would keep track on the status, whether it has started,on process or finish. If possible, add a Yes/No Datatype to identify which ones are active and inactive for reporting purposes.

Let me know if you need more info

jdraw · Jan 13, 2020

Interesting rail road tie plates.

What exactly is your role//responsibility in this "business"?
If you only want to know the current status of existing Jobs, then I advise you to not build a database. I say that because it seems to me you are working with a small aspect of the "apparent business".

If, on the other hand, you take orders, assign work and monitor the entire process, then step back and identify all of the subjects/things involved and design a database based on your specifications/facts.

You may be dealing with
Customer
Job
JobSteps and status,
PlateTypes
PlateMachining
PlateMilling
PlateInspection
Inspector
Prices
QualityLevels
.....

Winnipeger · Jan 14, 2020

Thank you all. I am learning a lot of things from you guys

In response to plog and jdraw. Sorry for not giving the whole picture, yes it does includes orders,assigning of work and tracking them.

I now understand your point, I do have to restructure my database to be able to come up with the desired output. I am now working on it and hopefully clear things up.

Again, appreciate your time and effort on this matter

Have a great day!

Yes/No Datatype (1 Viewer)

Winnipeger

Registered User.

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

plog

Banishment Pending

jdraw

Super Moderator

Pat Hartman

Super Moderator

Winnipeger

Registered User.

Attachments

plog

Banishment Pending

Winnipeger

Registered User.

plog

Banishment Pending

Winnipeger

Registered User.

jdraw

Super Moderator

Winnipeger

Registered User.

Similar threads

Users who are viewing this thread