Completely New to Access

[Gaffnr]

Hi All,
This will probably infuriate all you access users out there but I am
completely new to this software.

I have managed to import an excel workbook into access. this new access db
contains details of monies owed between companies. Some debts for the same
relationship are on multiple lines.
for example,
Company A owes company B 100
Company A owes company B 500
Company A owes company B 300
Company A owes company C 250
Company D owes company A 360

etc etc
What I want to do now is create an overlay so that persons in each company
can pick up their positions with each company.
For example, the person in company A can see that they owe 900 to company B
(i dont want to see three lines of 100,500 & 300 that make up the 900, just
want to see the total), they owe company C 250 and are owed 360 by company D.

So, what I want is the person in company A to go into the overlay, choose
company A and it will show its payable or receivable position which each
company.
Likewise, I want the person in company B to be able to do the same etc.

Thanks so much - this may not be possible but.....

 

[Gaffnr]

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob
--
Rob Gaffney

Of course you won't infuriate anyone. Everyone was new to Access at some
point.

Not knowing what kind of a user interface you are looking for or the table
details here is an overview: The easiest way to do this would be with a
parameter query. Create a query with a company field and in the criteria
for the company put: [What company?] - the user will be prompted for the
company when you run the query. Click the SIGMA symbol on the top menu and
set the Company field to GROUP BY, include the field with the amount owed and
make it a sum for total. The query will show the total sum owed for the
company indicated. All of this can be done more elegantly in forms and with
VBA or macros.

Bonnie
http://www.dataplus-svc.com

Hi All,
This will probably infuriate all you access users out there but I am
completely new to this software.

I have managed to import an excel workbook into access. this new access db
contains details of monies owed between companies. Some debts for the same
relationship are on multiple lines.
for example,
Company A owes company B 100
Company A owes company B 500
Company A owes company B 300
Company A owes company C 250
Company D owes company A 360

etc etc
What I want to do now is create an overlay so that persons in each company
can pick up their positions with each company.
For example, the person in company A can see that they owe 900 to company B
(i dont want to see three lines of 100,500 & 300 that make up the 900, just
want to see the total), they owe company C 250 and are owed 360 by company D.

So, what I want is the person in company A to go into the overlay, choose
company A and it will show its payable or receivable position which each
company.
Likewise, I want the person in company B to be able to do the same etc.

Thanks so much - this may not be possible but.....

 

[Golfinray]

There are many snares when a newbie tries to create a database. Great
learning experience! I would suggest that you go to many of the MVP's sites
and get sample databases that have already been written in a basic form to do
exactly what you want to do. You will save many headaches by following some
good examples. Start with www.allenbrowne.com

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob
--
Rob Gaffney

Of course you won't infuriate anyone. Everyone was new to Access at some
point.

Not knowing what kind of a user interface you are looking for or the table
details here is an overview: The easiest way to do this would be with a
parameter query. Create a query with a company field and in the criteria
for the company put: [What company?] - the user will be prompted for the
company when you run the query. Click the SIGMA symbol on the top menu and
set the Company field to GROUP BY, include the field with the amount owed and
make it a sum for total. The query will show the total sum owed for the
company indicated. All of this can be done more elegantly in forms and with
VBA or macros.

Bonnie
http://www.dataplus-svc.com

Hi All,
This will probably infuriate all you access users out there but I am
completely new to this software.

I have managed to import an excel workbook into access. this new access db
contains details of monies owed between companies. Some debts for the same
relationship are on multiple lines.
for example,
Company A owes company B 100
Company A owes company B 500
Company A owes company B 300
Company A owes company C 250
Company D owes company A 360

etc etc
What I want to do now is create an overlay so that persons in each company
can pick up their positions with each company.
For example, the person in company A can see that they owe 900 to company B
(i dont want to see three lines of 100,500 & 300 that make up the 900, just
want to see the total), they owe company C 250 and are owed 360 by company D.

So, what I want is the person in company A to go into the overlay, choose
company A and it will show its payable or receivable position which each
company.
Likewise, I want the person in company B to be able to do the same etc.

Thanks so much - this may not be possible but.....

 

[John Spencer]

Or even easier to get that is to use the menu when you have the query in
design view.

VIEW: Totals

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

It's the Greek letter S (the letter sigma) on the top menu that you use to
group. Click it and notice that you have another row in the query that you
can either group by, sum, count, etc. Search help on GROUP BY Query.

Bonnie
http://www.dataplus-svc

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob

Of course you won't infuriate anyone. Everyone was new to Access at some
point.

[quoted text clipped - 39 lines]

Thanks so much - this may not be possible but.....

 

[Gaffnr]

thanks - i got it but its doing weird things. can i jsut ask one more
question....

I have three fileds
Company
Parter Company
Value

My parameter is on company and this lists ALL lines of debt relationship and
the prompt asks me to choose my company.

The total I tried to do is on the value of course but sum it by partner
company. I put the group in parnter company of sum and leave all other
totals alone but it does not return the correct numbers -its misses line and
adds another new line into the partern company!
--
Rob Gaffney

It's the Greek letter S (the letter sigma) on the top menu that you use to
group. Click it and notice that you have another row in the query that you
can either group by, sum, count, etc. Search help on GROUP BY Query.

Bonnie
http://www.dataplus-svc

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob

Of course you won't infuriate anyone. Everyone was new to Access at some
point.

[quoted text clipped - 39 lines]

Thanks so much - this may not be possible but.....

 

[Gaffnr]

Thanks John - I got that way too now.
Rgds
Rob
--
Rob Gaffney

Or even easier to get that is to use the menu when you have the query in
design view.

VIEW: Totals

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

It's the Greek letter S (the letter sigma) on the top menu that you use to
group. Click it and notice that you have another row in the query that you
can either group by, sum, count, etc. Search help on GROUP BY Query.

Bonnie
http://www.dataplus-svc

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob
Of course you won't infuriate anyone. Everyone was new to Access at some
point.
[quoted text clipped - 39 lines]
Thanks so much - this may not be possible but.....

 

[Gaffnr]

Thanks - ill give it a go although I doubt i can find an example that covers
what I need. thanks for the reply - much appreciated
--
Rob Gaffney

There are many snares when a newbie tries to create a database. Great
learning experience! I would suggest that you go to many of the MVP's sites
and get sample databases that have already been written in a basic form to do
exactly what you want to do. You will save many headaches by following some
good examples. Start with www.allenbrowne.com

thanks so much bonnie. If you dont mind, im struggling a little.
I have managed to create the query and set the criteria which pops up the
"what company?" prompt. Love it!

When I select one of the companies, it does indeed show all the related
lines, however, i cant find the SIGMA thing to group the totals. Ive even
done a help on SIGMA and nothing. Im using Access 2003.

Also, Ive tried to build a form over the query and when i select my company,
it shows record 1 of x (i.e. it does select only records for that company but
diplays one at a time whereas i want to see all records on one form like i do
on the query)

I thank you so much for any help,
rgds
rob
--
Rob Gaffney

Of course you won't infuriate anyone. Everyone was new to Access at some
point.

Not knowing what kind of a user interface you are looking for or the table
details here is an overview: The easiest way to do this would be with a
parameter query. Create a query with a company field and in the criteria
for the company put: [What company?] - the user will be prompted for the
company when you run the query. Click the SIGMA symbol on the top menu and
set the Company field to GROUP BY, include the field with the amount owed and
make it a sum for total. The query will show the total sum owed for the
company indicated. All of this can be done more elegantly in forms and with
VBA or macros.

Bonnie
http://www.dataplus-svc.com

Gaffnr wrote:
Hi All,
This will probably infuriate all you access users out there but I am
completely new to this software.

I have managed to import an excel workbook into access. this new access db
contains details of monies owed between companies. Some debts for the same
relationship are on multiple lines.
for example,
Company A owes company B 100
Company A owes company B 500
Company A owes company B 300
Company A owes company C 250
Company D owes company A 360

etc etc
What I want to do now is create an overlay so that persons in each company
can pick up their positions with each company.
For example, the person in company A can see that they owe 900 to company B
(i dont want to see three lines of 100,500 & 300 that make up the 900, just
want to see the total), they owe company C 250 and are owed 360 by company D.

So, what I want is the person in company A to go into the overlay, choose
company A and it will show its payable or receivable position which each
company.
Likewise, I want the person in company B to be able to do the same etc.

Thanks so much - this may not be possible but.....