3-way Boolean?

[MarcieFess]

OK, I know you can't do a 3-way Boolean! But I'm wondering if there's a way to accomplish what I need:

I have a field that I created as a Text field, but I need it to be Boolean in order to be able to have Access put an 'X' next to the correct entry on a printed report.

There are 3 choices: Storage, Closed, Open

If a product is only sold out of "storage", never 'used' in the store (such as for giving out samples, etc.)...then my Storage field would by set to Y

However, if it's set to "no", I need it to force a response to the Closed field...

So, if an item is only sold from storage, there would be a Y response to Storage, and Closed would be empty

If the response to Storage is "N", then there MUST be a response to Closed...either Y (if it's 'used' closed) or N (if it's 'used' open)

Is there a way to set this up in the table?

 

[jdraw]

Have you considered a Lookup Table ( my choice of name may not be appropriate)

tblProductStatus
ProdStatusId   PK
ProdStatusDesc  text

with data values

1   Storage
2   Closed
3   Open

I'm not sure why there has to be an X????

 

[MarcieFess]

That's the way I have it set up in the table now.

The report is a government report, so it has to be formatted their way.

When the report is run, each page has to indicate the Use type: There has to be ONE check mark next to either "Open", "Closed", or "Storage"

Right now I have 1 field that accepts O, C, or S

In order to have the program print a check mark or an "X" next to the appropriate word, doesn't it have to be a Boolean field in the first place? That's the advice I've gotten so far.

It's been a pretty complicated report and I have the rest of it working with the help and advice of people here on the these forums...this is the last piece before it's complete.

 

[jdraw]

I think you have to adjust the source data for the report to have the field value you want in the data; and some logic to position the X (by the sound of things).

 

[jdraw]

I have no idea what the report looks like, and specifically the place where the "X" has to go.

You could determine which value of prodStatusId is involved and then create a text string to offset the X.
this sort of thing

Iif ProdStatusId = 1," X      "
Iif ProdStatusId = 2, "   X    "
Iif ProdStatusId = 3,"       X "

 

[MarcieFess]

This is what that part of the report looks like:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=45377&stc=1&d=1354315109

Additionally the report is grouped by AisleNumber...it can also be grouped by Inside/Outside (each set have to be printed separately; if there's an aisle number it's all inside)

The Use Open/Use Closed/Storage is the "3-way Boolean". Either it's Storage, or it's Used. If it's Used, is it Open or Closed? Only 1 can be checked and again, each type have to be printed on separate pages of the report.

My issue right now is getting the logic of the table correct; I can do the Group Bys to get the reports.

 

[jdraw]

What exactly does this mean?

My issue right now is getting the logic of the table correct

Want to post a dumbed down version of your db --- nothing confidential/personal?

 

[MarcieFess]

My data tables:
tblCompany
CompanyKey
CompanyName
CompanyAddress
CompanyPhone

tblStoreInformation
StoreKey
StoreName (allows duplicates as more than 1 company may name their stores with the same name)
StreetAddress
City
State
Zip
CompanyKey (links the Company to the Store)

tblDepartment
DepartmentKey (the '1' side of the link to tblStoreDepartment; also the '1' side to the link to Product table)
DepartmentName

tblHazardClass
HazardKey (the '1' side to the Product table)
HazardClass
Type
LimitAmountInside
LimitAmountOutside
ExemptAmountOpen
ExemptAmountClosed
ExemptAmountStorage

tblStoreDepartment
StoreDeptKey
StoreKey (the 'many' side link to the tblStoreInformation)
DepartmentKey (the 'many' side link to the tblDepartment)

tblStoreProducts
StoreProductKey
UPC (the 'many' side link to Product table)
StoreKey (the 'many' side link to tblStoreInformation)
Cases
Storage
Use
AnnualWaste

The BIG table:

Product
UPC (primary key, links to tblStoreProducts)
DepartmentName (linkes to tblDepartment)
HazardKey (linkes to tblHazardClass)
ProductName
Chemical
ChemicalAbstract
PhysicalState
Package
Size
Units
NFPAHealth
etc...many more fields not part of this issue

I also have several lookup tables that I use to make sure the users input correct values.

Users either work with existing companies, or they create a new one (a new client).

Within a particular company, they work with one particular store.
This brings up my first issue...these companies often refer internally to their stores by the town they are in...so many companies may have a "store name" that's the same...which is why I made that field "Duplicates Ok"

For that particular store, they may have to update product quantities, input brand new products that don't yet exist in the database, etc. They may have to create a new store, may have to update which departments are on which store aisles, etc.

Once they have the store quantities updated, they have to run 3 reports and print the results.

One of the reports is printed by AISLE (and for each Aisle, a new page for each USE (which can be Storage, Open or Closed) and a new page for each Storage (Inside or Outside)

The second report is printed by HazardClass. That one is finished and is printing perfectly.

The third one is going to be another challenge...printing an X'd box next to the appropriate option (yes or no) based on totals for each HazardClass compared to quantities required for permits.

 

[MarcieFess]

So, first I guess I need to determine whether I've got the tables set up correctly in the first place.

The bolded fields in tblHazardClass may be unnecessary completely...when I first set up the tables, I thought I'd be referring to those totals but now it appears I may just hard code the amounts into the report. My only question is if those amounts may be different from state to state. Right now we are working only in Texas stores.

The fields Storage and Use are in the tblStoreProducts table, as different stores or companies may handle this differently. One store may keep certain products outside while another store may keep them inside (Storage field), and some companies or stores may sell only out of Storage ('S' entry in the Use field), or they may Use the products inside the store...in that case, do they 'use' them Open ('O') or Closed ('C')?

Think about nail salons...they "use" nail polish "open"...they open it inside their establishment and "sell" it to the customers through "using" it.

To sell something "Use Closed"...would be something like a store purchasing a bunch of trial-sized or travel-sized items...maybe hundreds of shampoo samples. They "use" them by giving them as samples (they don't "sell" them out of "storage"), but they are used "closed"...for our purposes, no vapors are liberated during the "use" while the product is inside the store.

I probably named the fields incorrectly..."Storage" is where they indicate whether the product is kept Inside or Outside (the large Blue Rhino propane tanks are always Outside). "Use" is where they indicate whether it's "used" which for our purposes means "SOLD"...from storage, from being used closed, or from being used open.

It's difficult to change this field, with 3 possible values, to a Boolean. The other field (Storage) is easy to make into a Boolean field.

 

[jdraw]

It's a lot to absorb in a post . It seems a little late to be wondering about your tables and relationships, seeing as you now have some reporting issues. By the way I did read your introduction --welcome.

Have you designed your tables and relationships to be Normalized? Your terminology is great for you and your project but I'm afraid I'm missing the purpose of the database and some of the relationships (and rationale) that you have described.

Can you post a jpg of your Relationships? That might be helpful to some readers.

Remember, the more we understand of your situation and environment, the better a reader has of offering targeted advice/assistance.

As I often suggest to posters with "big issues or short deadlines or those in jargon mode":

pretend you're in the line up at McDonalds, turn to the person behind you - who doesn't know you; has no idea of what a database is; no familiarity with Access or software or your business/company --- in 5 lines WHAT would you tell him/her what the issue is? That's the basis of what the reader needs to help you.

I will read your materials again.

Good luck with your project.