Need help with stock exchange database

[henjin]

Hello my friends,



I really appreciate the existence of such a forum because it gives a possibility that noobs like me learn how to use Access as it should be. I just want to inform you that I am a complete noob what it is regarding MS Access... I know the basics, what are tables, queries etc.

The problem now is... i need for my work to create a database for my coworkers where they can monitor their stock tradings... as they are involved in multiple diverse trading companies and are beginning to loose overview...

I need to make a table where they input every trade individually, with information about with which trading company they made the investment, what stocks they traded and under which price did they bought. Then i need to make a filter where it should show the grouping for every company every coworker and every stock (sum of each different stock individually) then i need to make a portofolio separately for every coworker that he can see which stock he has and what the current value of it is (that means it needs two additionally fields one where he can input the current price and the second where it calculates the current value (price*sum of the stock he owns).

For now i created like this:
- made a main list where they input every trade
-created a crosstab query where the informations are grouped like this: Trading company, Name, Last name, Stock 1, Stock 2, stock 3... etc (example: JP Morgan, Michael, Soth, 4012(owns that much stocks of the named stock), 334, 4421)

For now it works ok, as this is what i need. Now I need to know how to make a query out of this crosstab where i will have the given fields in which they can insert the price of the stock and a field that counts the value.
For example:

Michael Soth

Stock 1 4012 (empty field to input the price) (field that calculates the value 4012*(price))
Stock 2 334 (empty field to input the price) (field that calculates the value 334*(price))
Stock 3 4421 (empty field to input the price) (field that calculates the value 4421*(price))

How can I create such a query or do you have a better solution for this?

Much much thanks upfront.

[June7]

Cannot base data entry on crosstab queries as they are not editable because they are by nature an aggregation of data.

Might need form/subform arrangements for data entry.

Review this thread for guidance on db design: http://forums.aspfree.com/microsoft-...es-208217.html

[henjin]

Well update on the current situation... I have resolved the problem with the calculation using forms and calculated fields in it... but now I have an other problem... I have imput a combo box which shows all trades filtered by the unique coworkers ID number now i need in the form a box that calculates the average price of entry in the market and a box that shows the percentage earned or lost for each different stock owned... i have thought about a if/then command combined with the avg function: example " IF [Combo_box].form![Stock]="Microsoft" THEN AVG([Combo_box].form![Price] ELSE 0 " and how could I create a box that, when I input in my existing "Price now" box calculates the percentage of difference between the average price and that "Price now" box.

Thank you upfront and I gotta confess it start to become very interesting and challenging working in Access. I like it a lot

[June7]

The Avg function won't work that way in an expression. Avg is an aggregate function used in GROUP BY (totals) queries.

Could use DAvg function. That is a domain aggregate function. Access Help has info on those functions.

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[henjin]

I kinda don't get it
I definitely should have payed more attention in class when we worked in Access... Well here is the db... the form Depots needs that field with the average value of the Field "Kurs" from that combo box that is included for every value separate... and another to calculate the percentage in difference of that average Kurs when I imput the current one in the field "Kurs" on the form itself...

Thank you upfront

[June7]

Having a hard time following what you are trying to do.

Now I need to know how to make a query out of this crosstab where i will have the given fields in which they can insert the price of the stock and a field that counts the value.

What do you mean by 'insert' - cannot enter data through a crosstab query or any query based on a crosstab.

[henjin]

srry my mistake, haven't explained it properly... First i have created the crosstab which groups for every coworker their stocks then i have created a form which shows those values... in that form i have created one unbound text field where you can input the current price and a second one which calculates the value in € of those stocks... in that form i have also added a combo box which shows every individual trade that has been inserted in the main table for that person... what i tried to create is a box in the form... which shows the average price of that particular stock where it takes the data out of the combo box... looks like i have taken a bite to much because i can not figure out how to program that particular box...

[June7]

I am still confused by what you describe. There is no combobox on the form. Do you really mean the subform that lists individual trade records?

You want average for each worker for each Werte? There are 4 Werte values but you have only 3 showing on the form.

See if this gets you what you need.

1. rename Depots query to DepotsSummeCT
2. do another crosstab query to calculate average of Kurs - copy/paste first query and rename to DepotsAvgCT and change the Value field to Avg Kurs
3. join the two crosstab queries and name Depots
SELECT DepotsAvgCT.*, DepotsSummeCT.[Daimler Ag], DepotsSummeCT.Infineon, DepotsSummeCT.Samsung, DepotsSummeCT.Singulus
FROM DepotsSummeCT INNER JOIN DepotsAvgCT ON DepotsSummeCT.KDNummer = DepotsAvgCT.KDNummer;
4. correct ControlSource properties for the bound boxes and the formulas for calculated boxes

[henjin]

I am still confused by what you describe. There is no combobox on the form. Do you really mean the subform that lists individual trade records?

You want average for each worker for each Werte? There are 4 Werte values but you have only 3 showing on the form.

See if this gets you what you need.

1. rename Depots query to DepotsSummeCT
2. do another crosstab query to calculate average of Kurs - copy/paste first query and rename to DepotsAvgCT and change the Value field to Avg Kurs
3. join the two crosstab queries and name Depots
SELECT DepotsAvgCT.*, DepotsSummeCT.[Daimler Ag], DepotsSummeCT.Infineon, DepotsSummeCT.Samsung, DepotsSummeCT.Singulus
FROM DepotsSummeCT INNER JOIN DepotsAvgCT ON DepotsSummeCT.KDNummer = DepotsAvgCT.KDNummer;
4. correct ControlSource properties for the bound boxes and the formulas for calculated boxes

Srry my mistake again... i am not familiar with those terms i just said what I inserted into the form, it was a combo box but it seems that the right term is sub-form. I must thank you for your patience and the will to help me. I appreciate it very much. I just have one question, the "SELECT DepotsAvgCT.*, DepotsSummeCT.[Daimler Ag], DepotsSummeCT.Infineon, DepotsSummeCT.Samsung, DepotsSummeCT.Singulus
FROM DepotsSummeCT INNER JOIN DepotsAvgCT ON DepotsSummeCT.KDNummer = DepotsAvgCT.KDNummer;" where do I input this? In the form or in the new query that I need to create?

[June7]

That is a query. You can build it as a saved Access query object and then refer to that query as the form RecordSource or paste that sql statement directly into the form RecordSource property.