Help - many small issues from an access novice

[Armitage2k]

Hello everyone!

to make it short and simple, my total working experience with Access comes down to 3 days now, in which i successfully imported tables, designed forms, made queries, etc., so actually am quite successful, but now definitely am at the end of my knowledge.

I am currently working for a small investment company, and decided to create a database to keep track of the customers. however, after adjusting the MS Students Template for Access 2007, I finished this task but now got asked by my colleagues to make this database more usable for all departments of the company. And this is where the problems begin...

We selling 8 different products to our customers, and usually each customer buys 1 (max. 2) different products. Once a month (or maybe in the future daily if this database works), our finance people will enter the investment amount of each specific client for each product he bought. I designed a form to view all data i need, divided by pages for each different department.

Now, what I have to do: Here a list of small issues
Form Client Details:
1. Adjust data source
This form is supposed to display details from multiple tables. I have created relations between the tables Products -> Clients -> Investment. I then created a query to have all the data I need together, but get a "Type mismatch" error when opening it, forcing me to use the original Clients table as data source (but of course this is useless as it doesnt give me the related data I need).

PAGE: GENERAL
2. The Client name show show me the FIRST and LAST NAME from the source, but instead gives me an error...
3. Purchased Products - I need a way to have displayed which products the client bought and then display it on the form. Note: I have not yet figured how I can keep track of customers who have bought 2 products in the table. Any ideas?

PAGE: INVESTMENT INFORMATION
4. Here I want to display the investment information for the selected client. finance will enter the data manually in the shown subform, however, I need a select box to filter the shown entries by INVESTMENT DATE to either show ALL entries, per WEEK, per MONTH, per YEAR. The totals for the investment amount, share and share 2 should be displayed in the boxes below the subform.

By the way, there are some tables and queries from the original template. they are not needed, but I am too afraid that nothing works if I delete them...

REPORTS:
I am sure I can do this myself, but if someone is really bored to help me out, I do appreciate it

Monthly Report - I need a report to display how many clients have bought WHICH product and how much investment they have for this product for the current month. My finance guys will run this once a month to see how the products are doing.

Contact Report - A need a report to show me contact information for all clients (first and last name, company, address, zip, state, country, email).


I know that this is a really cheap approach to get this done, and I assure all of you I would really like to learn this stuff, but I am just stuck and now am too busy to really get into this. I would like to use this database for my own development as it helps me to see how the professionals in this forum are working and hope that it helps me for my future projects in access.
Also, should someone see this as a little project and want to have sonme motivation, I gladly will invest some money into this if it helps.

From the bottom of my heart, THANK YOU FOR ALL YOUR HELP!
Armitage2k

 

[statsman]

Just a few thoughts off the top of my head.

1. Data type mismatch. Is it possible you have created the same field in different tables with different data types? Example, Client Number in table 1 is text and Client Number in table 2 is number?

2. Are the first and last names in different fields and you are attempting to get them into one field on your form?
If so, double check your coding. In your query try :
FullName[first]&" "&[last])

3. You are getting into much more complicated (but not impossible) functions.
You can show everything the client has bought since they became a client, or you can show how much of a specific product or how much of each they have bought over a specific period. Each would require a separate query.

4. Really an offshoot of 3 as the same query that runs the form can also be used to run a report.

Good luck in your database and welcome to the Access brotherhood.

 

[statsman]

I just noticed there was a grumpy face in the post.

That should be a round bracket followed by a square bracket.

 

[SOS]

I just noticed there was a grumpy face in the post.

You can fix that by going into edit the post and then checking the checkbox which says DISABLE SMILIES.

 

[Armitage2k]

@ statsman
thanks for pointing me there. the first problem with the expression error resulted due to the a text field in the investment table. solved.
the second issue about the multiple products per client, I simply solved by relating the products tabel to a seperated client id, showing me 2 identical entries if the same client bought 2 different products. It is not a very elegant solution but does the job.

the investment problem still remains. HOW can I calculate, resp. filter entries per date, by using a control from the form (instead of letting the user select the filter as I dont want them to mess with the records)?
And regarding the report, it does happen to be a pain in the @$$, because I have no clue how to do

thanks though for pointing me the right way. I used almost my whole day off, but at least I know better next time Life and learn, right?

Thanks,
A2k