• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

From Excel to Access (1 Viewer)

SweetAngel007

New member
Local time
Tomorrow, 03:22
Joined
Sep 19, 2020
Messages
4
A pleasant day to all.
I have these Table queries from Excel Need to have these transferred to Access. hope you can help :)

FIELD4 = IF([FIELD1]="",0,1)
FIELD5 = COUNTIFS([FIELD1],[@FIELD1],[FIELD2],[@FIELD2])
FIELD6 = COUNTIFS([FIELD1],[@FIELD1],[FIELD2],[@FIELD2],[FIELD3],">1")
FIELD7 = IF([FIELD6>0,1,0)
FIELD8 = IF(FIELD7=0,1,0)*FIELD4
FIELD9 = FIELD8 / FIELD4
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:22
Joined
Aug 30, 2003
Messages
34,642
The Access equivalent of the IF() function is IIf(). I haven't used COUNTIFS so maybe if you describe the result you want to get we can come up with something.

Generally you wouldn't save calculated data, but there is a calculated data type if your situation merits it:

 

SweetAngel007

New member
Local time
Tomorrow, 03:22
Joined
Sep 19, 2020
Messages
4
Thanks for the Reply.
COUNTIFS are used if you have 2 or more fields you need to check.
Sample Table:
FIELD1FIELD2FIELD3FIELD4FIELD5FIELD6FIELD7FIELD8FIELD9
12354IEEAST133100.00%
12354SAFARIEAST11001100.00%
12354IEEAST133100.00%
5646513SAFARIWEST11001100.00%
SAFARIEAST00000
12354IEEAST133100.00%
4854SAFARIWEST11001100.00%
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:22
Joined
Aug 30, 2003
Messages
34,642
I'm not clear on what you're counting, but take a look at the DCount() function.
 

plog

Banishment Pending
Local time
Today, 14:22
Joined
May 11, 2011
Messages
10,080
The best way to describe data manipulations is with sample data. I suggest you post 2 sets of data to demonstrate what you have and what you want:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to produce when you feed it the data from A.

Again, 2 sets of data where B is tied to A and demonstrates exactly your expectations.
 

Users who are viewing this thread

Top Bottom