Adding Value to a field when condition is met

[Gabynex9]

I have not been able to find the way to ask this so google has been of no help so if you could name something that does this that would be great!
I need for a valued to be changed in a row after it checks for how many rows have another value, counting how many and that number making it the amount field.
Example:
Fields- Name sponsor amount
I want access to show how many people have been sponsored by Gabriel... So if 3 where sponsored by Gabriel show 3 to the amount field
for Gabriel row! Any way to do this or an easier way?
I for it to check it every time a user is added incase the user added is sponsored by Gabriel add it automatically! Thanks again!

[June7]

You want every record for Gabriel to show how many records Gabriel has? That is not necessary. An aggregate query can do the count.

SELECT [Sponsor], Count([Name]) AS Amount FROM tablename GROUP BY [Sponsor];

[Gabynex9]

You want every record for Gabriel to show how many records Gabriel has? That is not necessary. An aggregate query can do the count.

SELECT [Sponsor], Count([Name]) AS Amount FROM tablename GROUP BY [Sponsor];

Something like but no, not just Gabriel any name the would be inputed lets say... You enter harry well i would like the amount field check if there are
any sponsors with harry wich there should not be becouse he is a new sponsor. After I register someone with the sponsor field with the name harry apply that mount of people with harry in the sponsor to amount! Can this be done?

Lets say tom and billy where found by harry well, access should make that count in the sponsor field and add the amount to the amount field.

[June7]

Gabriel and all others. Yes, the aggregate query will do that. That's what the 'GROUP BY' clause is for. Did you try?

[Gabynex9]

Gabriel and all others. Yes, the aggregate query will do that. That's what the 'GROUP BY' clause is for. Did you try?

The instructions you gave me did not work, how would I use these in the query Im having a hard time figuring this out please help!

[June7]

Exactly what did you do? Why didn't it work - error message, wrong results, nothing happens? Post examples of raw data and desired output or provide db for analysis. Follow instructions at bottom of my post.

[Gabynex9]

Example, You see "Hiram" the row in my Extreme promotores tables right? I want to make a Query that checks the Pm-Sponsors column and see if anybody was sponsored by
Hiram, count how many time his name appears in the sponsor column and that value be presented in the Query but I want this process done for everyone so all the name show up
with the people they brought in. Then I would multiply this amount (people brought in by each name) and my action table record and receive the amount they are going to be paid. (The action table only has 1 value that can change over time)

[June7]

Don't see the query I suggested in the db. Consider:

SELECT [EXTREME PROMOTORES].[PM-SPONSOR], Count([EXTREME PROMOTORES].[PM-NOMBRE]) AS [CountOfPM-NOMBRE], Max(Acciones.[accion]) AS Rate
FROM [EXTREME PROMOTORES], Acciones
GROUP BY [EXTREME PROMOTORES].[PM-SPONSOR];

[Gabynex9]

Don't see the query I suggested in the db. Consider:

SELECT [EXTREME PROMOTORES].[PM-SPONSOR], Count([EXTREME PROMOTORES].[PM-NOMBRE]) AS [CountOfPM-NOMBRE], Max(Acciones.[accion]) AS Rate
FROM [EXTREME PROMOTORES], Acciones
GROUP BY [EXTREME PROMOTORES].[PM-SPONSOR];

The query you said I didn't put it becouse everytime I past it I get an error, Some chunk of the expressions might somewhere but I dont know this, Do i just past all of the expression into the build? Im a beginner, really! And thanks for your patience!

[June7]

The first query was just an example and you would have to replace with your actual table and field names because I did not know them.

Copy/paste the second suggested SQL statement into SQL View of query builder then switch to Design View to see that structure.

I built that query with the query builder. After opening the query builder and selecting the table, I clicked the TOTALS button on ribbon. This makes aggregate functions available.

[Gabynex9]

The first query was just an example and you would have to replace with your actual table and field names because I did not know them.

Copy/paste the second suggested SQL statement into SQL View of query builder then switch to Design View to see that structure.

I built that query with the query builder. After opening the query builder and selecting the table, I clicked the TOTALS button on ribbon. This makes aggregate functions available.

Yes it did the count but in a normal builder I would do [table]*[table] to multiply the values how would I multiply the COunt of the value I get with accion I also get? That would be all

[June7]

First build the aggregate query. Then use it in another query or as the source for a report. Do the multiplication calc there.

[Gabynex9]

First build the aggregate query. Then use it in another query or as the source for a report. Do the multiplication calc there.

Yeah, im not getting that... Sorry im not really good at all this.

[June7]

Not getting what? How to open query builder? How to select a query as data source for another query? How to build expression in query? How to use query as RecordSource for a report?

[Gabynex9]

Not getting what? How to open query builder? How to select a query as data source for another query? How to build expression in query? How to use query as RecordSource for a report?

Got it thanks man