ACCESS - Easy QUERY (Help)

[efernandes]

HI,

I have the next 3 tables,

*************************************************
Table USER: id_user (primary key)
name

Rec: id_user, name
------------------
rec: 1, A
rec: 2, B
rec: 3, C

*************************************************
Table AP : id_ap (primary key)
nr_ap (primary key)
id_user
id_sort

Rec: id_ap, nr_ap, id_user, id_sort
-------------------------------------
rec: 1, 2, 1, 1
rec: 2, 5, 2, 1
rec: 3, 1, 3, 1
rec: 4, 3, 1, 2
rec: 5, 4, 2, 2
rec: 6, 3, 3, 2

NOTE:
SELECT sum(nr_ap) AS Total FROM AP

with id_sort = 1 -> SUM(nr_ap) = 2+5+1 = 8
with id_sort = 2 -> SUM(nr_ap) = 3+4+3 = 10
*************************************************
Table SORT: id_sort (primary key)
value

Rec: id_sort, value
------------------
rec: 1, 80
rec: 2, 200

**********************************************
I need do create a QUERY that for all records in SORT table, it will
show only for user 'A', the rescords as in the result shown below.

NOTE: xxxx = (value\SUM(nr_ap))*nr_ap

RESULT EXPECTED
------------------
id_sort, name, ap, value, xxxx

1 A 2 80 20 (80\8)*2 = 20
2 A 3 200 60 (200\10)*3 = 60


Regards,
Elio

 

[Keith Nichols]

Reserved word alert Access uses 'Name' itself. If you use this moniker, you will get all sorts of problems. Retitle your field to somethng else like "EmpName" for instance.

Do this then Zip your db and post it if it has no sensitive data etc.

Regards,

 

[Moniker]

You really should rename it, although putting the word name in brackets (like [Name]) will tell Access it's a variable name. You always want to avoid doing that to begin with.

Mainly, I'm responding because Keith said, "If you use this moniker, you will get all sorts of problems."

I swear, I come in peace! I'm just trying to help!

~Moniker (which, yes, means "name")

 

[Keith Nichols]

You really should rename it, although putting the word name in brackets (like [Name]) will tell Access it's a variable name. You always want to avoid doing that to begin with.

Mainly, I'm responding because Keith said, "If you use this moniker, you will get all sorts of problems."

I swear, I come in peace! I'm just trying to help!

~Moniker (which, yes, means "name")

Moniker,

I hadn't come accross you at the time of writing the orignal response and would not have used your handle if I had. Coincidentally, you have been a great help responding to my postings on the same day I wrote it. Kind of a small world thing I guess. I can confirm to any that you do come in peace!