Couple of Newbie Access Questions

[DeMarcus]

Okay, I have two questions.

First, if I have a table, and I want to combine two records together, how would I accomplish this? For example, say I have the following table.

NAME  SALES
Bill      2000
Jim     500
Ted    1000

And by some miracle of science, Bill and Jim are able to fuse together to become Jill, combining their sales together. So the new table would look ilke this.

NAME  SALES
Jill      2500
Ted    1000

My second question is, say I have an extended list of the same table with 30 names and corresponding sales. However, I want to find out Ted's percentage of sales of the TOP 15, not all 30. How can I accomplish this?

Thanks so much in advance for helping out a complete newbie.

 

[whitesmoke]

i don't see the first part happening unless you have a 3rd field with some kind of relational capability.
the second part can be achieved through a subselect using TOP and MAX methods.

eg:

select sum(amount) from (select top 15 max(sales) from tablename)

 

[Banana]

If Bill and Jill is in same table, you may be able to accomplish this by self join. Search the forum on self-join.

Beside, if you're trying to combine people into department or teams, you could insert a column signifying which team they are on and run a query showing sales per team, which is "correct" way, unless you had a very very good reason for doing what you dsecribed above.

 

[DeMarcus]

Thanks for the advice. I'll look into the solutions you guys provided.

As for the reason for wanting to merge two records, the actual database I am trying to work with is a bunch of firms and companies.

When two companies merge into one, I want to be able to consolidate the data (they're all figures, except for the company name field) easily.

 

[DeMarcus]

Beside, if you're trying to combine people into department or teams, you could insert a column signifying which team they are on and run a query showing sales per team, which is "correct" way, unless you had a very very good reason for doing what you dsecribed above.

I have another question, which relates to the method you just mentioned.

If I wanted to do exactly that (show sales by team), how would I accomplish this via a query?

I know it seems like a real newbie question, but hopefully this will help everything else fall into place.

 

[Banana]

You use criteria field to filter the query. Therefore you'd drag in the ID, Sales Team field, and Sales field, then set Sales Team field to = "WhateverTheNameOfTeamItIs"

Do a search for criteria for query to get some ideas.

 

[DeMarcus]

You use criteria field to filter the query. Therefore you'd drag in the ID, Sales Team field, and Sales field, then set Sales Team field to = "WhateverTheNameOfTeamItIs"

Do a search for criteria for query to get some ideas.

Oh.. I was hoping there was a way to combine the numbers for each team in one query. For example..

Team A 2000
Team B 1000
Team C 9000

etc.

 

[Banana]

You can use Totals query to accomplish this. Be sure you turn on the Totals (hidden by default) by right clicking on the query grid and selecting the Sigma button.

Then have team set as "Group By" and "Sales" as SUM.

If you get stuck, post the SQL.

 

[DeMarcus]

You can use Totals query to accomplish this. Be sure you turn on the Totals (hidden by default) by right clicking on the query grid and selecting the Sigma button.

Then have team set as "Group By" and "Sales" as SUM.

If you get stuck, post the SQL.

Thanks so much. All of these answers have finally helped me to learn and accomplish my task!

 

[Banana]

Glad to hear it worked out great for you.