From Excel to Access

[SweetAngel007]

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]

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:

Microsoft Access tips: Calculated Fields

How to create calculated fields in a Microsoft Access database. Describes how to create the fields in queries, as well as how and when to store calculated results in a table.

allenbrowne.com

 

[SweetAngel007]

Thanks for the Reply.
COUNTIFS are used if you have 2 or more fields you need to check.
Sample Table:

FIELD1	FIELD2	FIELD3	FIELD4	FIELD5	FIELD6	FIELD7	FIELD8	FIELD9
12354	IE	EAST	1	3	3	1	0	0.00%
12354	SAFARI	EAST	1	1	0	0	1	100.00%
12354	IE	EAST	1	3	3	1	0	0.00%
5646513	SAFARI	WEST	1	1	0	0	1	100.00%
	SAFARI	EAST	0	0	0	0	0
12354	IE	EAST	1	3	3	1	0	0.00%
4854	SAFARI	WEST	1	1	0	0	1	100.00%

 

[pbaldy]

I'm not clear on what you're counting, but take a look at the DCount() function.

 

[plog]

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.