From Excel to Access (1 Viewer)

SweetAngel007

New member
Local time
Today, 15:18
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
Yesterday, 23:18
Joined
Aug 30, 2003
Messages
34,736
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
Today, 15:18
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
Yesterday, 23:18
Joined
Aug 30, 2003
Messages
34,736
I'm not clear on what you're counting, but take a look at the DCount() function.
 

plog

Banishment Pending
Local time
Today, 01:18
Joined
May 11, 2011
Messages
10,126
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