Where to put Excel formuale into Access?

[Dave Lambert]

Hi All!

I'm not sure if I should be posting my question in this area of the forum or another area of the forum. If I'm posting it in the wrong place please let me know and I'll redo things.

We have an Excel spreadsheet (Office 365) in which we track trucking companies and their contracts and insurance coverage. I'm trying to convert this into an Access database (Office 365).

There are three fields/columns in the spreadsheet that are automatically placed in the spreadsheet. I want Access to perform these same checks and place the appropriate wording into the same fields in the Access table.

Where in Access should this be placed?

Thanks in advance for your efforts!

Attached is a screenshot of that part of the spreadsheet that shows what I'm trying to describe.



Here are the formulae in the spreadsheet. I pseudo-coded what is being checked and updated in case you don't understand the formulae. Anyway, here it is...

In Carrier Approved field/cell (A3):

=IF(OR(B3="Approaching",C3="Approaching"),"Approac hing",IF(AND(B3="Yes",C3="Yes"),"Approved","Not Approved"))

A3 = Carrier Approved
B3 = Current Contract
C3 = Current Insurance

If Current Contract = “Approaching” OR Current Insurance = “Approaching”
Carrier Approved = “Approaching”
Else
If Current Contract = “Yes” AND Current Insurance = “Yes”
Carrier Approved = “Approved”
Else
Carrier Approved = “Not Approved”


EndIf
EndIf


In Current Contract field/cell (B3):

=IF(AND($A$1<=Q3,$A$1+14>=Q3),"Approaching",IF($A$ 1>Q3,"No","Yes"))

$A$1 = Current Date (12/17/2018)
B3 = Current Contract
Q3 = Contract Expires Date (07/31/2020)

If Current Date <= Contract Expires Date AND Current Date + 14 >= Contract Expires Date
Current Contract = “Approaching”
Else
If Current Date > Contract Expires Date
Current Contract = “No”
Else
Current Contract = “Yes”
EndIf
EndIf


In Current Insurance field/cell (C3):

=IF(AND($A$1<=T3,$A$1+14>=T3),"Approaching",IF($A$ 1>T3,"No","Yes"))

$A$1 = Current Date (12/17/2018)
C3 = Current Insurance
T3 = Insurance Expires Date (10/24/2019)

If Current Date <= Insurance Expires Date AND Current Date + 14 >= Insurance Expires Date
Current Insurance = “Approaching”
Else
If Current Date > Insurance Expires Date
Current Insurance = “No”
Else
Current Insurance = “Yes”
EndIf
EndIf

[Dave Lambert]

Well, I see the editor removed all of my formatting of the pseudo-code. Is there a way to keep that formatting?

[CJ_London]

Yes use it the code tags - the# key . Highlight the code and hit the # key

[June7]

Calculations can be in query or textbox on form/report. Saving the calculated result will require code (macro or VBA) so don't save, calculate when needed.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Better would be ContractExpiresDate or Contract_Expires_Date, otherwise must enclose in [ ]: [Contract Expires Date].

IIf(ContractExpiresDate BETWEEN Date() AND Date() + 14, "Approaching", IIf(Date() > ContractExpiresDate, "No", "Yes"))

or

Switch(ContractExpiresDate BETWEEN Date() AND Date() + 14, "Approaching", Date() > ContractExpiresDate, "No", True, "Yes")

More complicated calcs could mean writing custom VBA function to handle.

[Dave Lambert]

Man, I'm clueless on this. I'm thinking I want to have all of the rows updated at once. Preferably each time the database is opened.

I'm trying to create an Update query but it doesn't like the criteria I enter into the Criteria of the CarrierApproved field. I receive a message about invalid syntax. I have no idea why. I don't receive this error in the CurrentContract or the CurrentInsurance fields. Their formulae are a bit different but nothing is flagged when I enter the criteria.

Do I want to do an Update query? Should I be doing something else? What?

Grrr!

Attached is a screen capture of the syntax and the error message I receive.

I've been looking through Help and tutorials but I'm not understand something. Please educate me on this!

[Micron]

IF(OR / IF(AND is Excel syntax, which you cannot use in sql. Tip: often, the offending part will be highlighted if you switch to SQL view and try to run it from there. If the query is an action query (append/update/delete) and it's the first time I'm testing in this manner, I switch to Datasheet view rather than clicking Run, which will simulate running the query without actually running it.

I'm curious as to why your latest attempt looks nothing like what's in post 4...
In your research did you investigate the IIF function, including nested IIF's? Nested IIF would be the closest thing to IF(OR , but you were also given the switch option. Have to wonder what importance you placed on the advice to not store calculations because I see that your query example is attempting to do just that.

[CJ_London]

access is not excel, you are using an excel formula

try

iif(currentcontract="approaching" or currentinsurance="approaching","approaching",iif(currentcontract="Yes" and currentinsurance="Yes","Approved","Not Approved"))

chances are if you have an excel mentality you will have a bit of a climb to get into a database structure mentality

For example you are attempting to store a calculated value in your table which with a few exceptions is a big no no. Something you do in excel, not in a database. In a database you calculate when required based on your currentcontract and currentinsurance value - otherwise every time you change the value of one of these, you will need to run a query to update your carrierapproved field.

Also not clear what you are going to update to - your calculation is a criteria so will only select records where carrierapproved=yes, no or approaching.

I suspect all you need to do is put the calculation as a field in a query e.g.

SELECT iif(currentcontract="approaching" or currentinsurance="approaching","approaching",iif(currentcontract="Yes" and currentinsurance="Yes","Approved","Not Approved")) as carrierappproved
FROM tblCarrierInformation

[June7]

And if you want to UPDATE a field with calculated result then put the expression in the UPDATE TO cell, not CRITERIA.

[Dave Lambert]

I really don't know what I'm doing in Relational Databases but I'm trying to learn. I have many questions.

I'm trying to make this look like their Excel spreadsheet. I have a form set up in datasheet mode to look like their spreadsheet. How do I show the three 'calculated' fields if they are not supposed to be in the table? They want to be able to see that data in all the rows. They have filters in the spreadsheet so they can see only the Approved or the Approaching or the Not Approved statuses. When Not Approved appears I have to contact the insurance companies and/or the carriers to make sure the carriers have the correct coverage and amounts as required by my company.

Personally I don't like the design; I'd prefer to design it differently but...

I looked into Help trying to understand things. Some things I get and some I don't. What is a nested IF?

So I don't need an Update query? That's fine but I don't know what I need. I'll reread all of the posts to try to understand things better and then I'll have more questions.

I'm glad you guys know what you're doing. Seriously! Thank you!

[Dave Lambert]

I really don't know what I'm doing in Relational Databases but I'm trying to learn. I have many questions.

I'm trying to make this look like their Excel spreadsheet. I have a form set up in datasheet mode to look like their spreadsheet. How do I show the three 'calculated' fields if they are not supposed to be in the table? They want to be able to see that data in all the rows. They have filters in the spreadsheet so they can see only the Approved or the Approaching or the Not Approved statuses. When Not Approved appears I have to contact the insurance companies and/or the carriers to make sure the carriers have the correct coverage and amounts as required by my company.

Personally I don't like the design; I'd prefer to design it differently but...

I looked into Help trying to understand things. Some things I get and some I don't. What is a nested IF?

So I don't need an Update query? That's fine but I don't know what I need. I'll reread all of the posts to try to understand things better and then I'll have more questions.

I'm glad you guys know what you're doing. Seriously! Thank you!

[Dave Lambert]

As a point of reference attached are the headers from the form I created in Access. It pretty much mirrors the Excel spreadsheet. Some of the fields contain hyperlinks. Right now there is only one table and one form. Yeah, I know that's not good design but it's simple. That's what I'm dealing with.

[June7]

Table does have Calculated type field available but they have issues. Review https://www.accessforums.net/showthread.php?t=74933

Do calculations in query and use query as the RecordSource of form/report or do calculations in textboxes on form/report.

Have you completed an introductory tutorial book on Access? Really should spend a solid week with one to learn basics of relational database principles, Access functionality, programming concepts.

Understanding nested code is essential to programming. Nested IIf is just one implementation of nesting. http://www.simply-access.com/Nested_IIF_Statement.html

[Micron]

Here's my standard page of links for beginners in case you're really not into books (but books have some advantages I think). If you search for posts here by Orange, he often posts a large block of links for online tutorials. Perhaps if he sees this, he'll tell us if there is a 'sticky' thread in this forum that contains them or even create one if not. Then I could point people to it as well.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[Dave Lambert]

Micron, I don't have any idea what the Switch option/function is but I'll look it up.

VIP, I don't have any Access books. Which book(s) do you recommend? So far I've been trying to read up on things via forums and tutorial sites.

Are there any formal classes that can be taken concerning Access? If so I wonder if they would be worth taking.

[June7]

Here is my Access learning background:

A few computer science classes, 2 of which were programming (BASIC, Fortran).

One-on-one tutor on-the-job for Access.

Web searches.

Some old used books on VB6 (very similar to VBA in language and syntax).

Skimmed through an introductory book on Access which had a brief chapter introducing programming.

Some community colleges/universities might offer formal classes in Access, probably in their business/office support program.