Hi Tech Republic people.
I am currently working on a database for my company which is has been designed. for the following things.
1. Record customer detail.
2. How Terminal details
3. To be able to run queries on how may terminals have been sent out, how many have be returned, How out in the field and work out how much we back from them.
4. To be about to find out what extras have been sent to the customer and which I am not sure how I am going to do.
The first bit I need help with is the queries. To be honest tho,this is a long question.
I have created several queries that will show me how many have been installed that month and I have got it to do the same for returns. But I was thinking of merging them. To try and cut down on all the queries I have.
The think I would mind doing is to create it so there is one query that show the following.
1. the install date which would be the first of one month and the first of the next month
2. I would like to have it come up with the date this query was run so that would be the invoice date.
3. The next couple of part would then show the Terminal installed, Retrieved and in field terminals for that month.
4. Then what I need to query to do then once it has the information from part 3, it need to work out how much it needs to charge for the terminals that have been installed for that month as well as the ones in the field. This is were it gets trick, the in field ones are charged at full price (?5.40) and the ones that have been installed for that month are charged half price (?2.70).
5. This part is very similar to part 4, this part would then need to work out how much to charge for retrieval which is ?45.00 for returning it and ?2.70 for the final the monthly cost.
6. Once this has been parts 4 and 5 have been worked out it just needs to give the total income for that month.
8. Once the query works out the total income for that month. I then need it to work out how much would then be given to shares which would be 25% off the total in come.
9. Once the query has workout how much is take away after shars, it then needs to show how much will be coming to the company.
This is another part that need to be added but that is part of my second question.
I was asked to add something on the database so we can enter in when customers buy extras. Currently there are 140 records for customers and having to create a new table for each one is very long winded just for this.
What would be the best way to add something in the database to identify what customers have brought extras and the how much the company has made from it from those extras?
I hope that this makes sense to someone and they are about to help me.
If you have any questions in regards to this please give me a shout.
Thanks And Regards
Daniel Shaw
Windows Network Administrator.
