Web Development



Help With a queries in Access 2007

By The N Hammer ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Date range

by patb071 In reply to Help With a queries in Ac ...

For the date range we use

Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now())-0,0)

this takes everything from the first day of the last month to the lastday of the month. This may be your biggest problem. As long as you have your tables linked correctly with the information you need you should be all set. Any other questions just reply.

Related Discussions

Related Forums