General :: Automated Top 10 Customers Calculation?
Mar 7, 2014
I am in the process of putting together a database for use in a hotel to log details of customers, enquiries, and sales.
I have one table for storing customer contact details in ("Customers"), another table for storing enquiry details in ("Enquiries") and a third table for storing sales data in ("Sales").
Each record in Sales is linked to a customer from the Customer table, so with a simple query you can see what any given customer has spent their money on.
My boss would like to have a list of who her "top 10" customers are, over a given period, so she can work out who spends the most. I was wondering if there is a simple way to do this?
The only way to do this that I can think of is to run a query for every customer (to isolate their sales data), use the total function to add their sales together, note down the total, and input into excel (to work out the ranking). However, seeing as we have been collecting data for around a month now and have around 400 customers in the database this would clearly take a very long time. Is it possible to automate this process?
In MS Access i want to be able log jobs and be able to assign my stock to the jobs i have logged is this possible ? how to create a database to log jobs but how can i make it so when i add stock to the job it will remove it from my stock pool and assign it to the customer thus taking it out of stock.
I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.
Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.
I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?
I have a query that may have 6 - 7 different records for example each record contains a different email address, i need to get the first email then send and email to that address then go to the next record get that email address send an email to that email address until its gone through all the records...
I use this code to send an email, dont know how to loop through records and get the email...
Code: Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Dim strPath As String Dim strFileName As String Set appOutLook = CreateObject("Outlook.Application")
The project I'm working on is an action list with the following tables;
itemnumber - autonumber PK originator - text dateentered - date/time actiondescription - text assignedto - text targetdate - date/time forecastdate - date/time actual date - date/time remarks - text due - text (not sure if this will be correct)
Basically, when a new task is entered, all info will be manually filled in except last 3 fields. targetdate needs to be locked after entry, and forecastdate initially set to targetdate.
"due" should show how many days between dateentered and forecast date, and preferably update everytime the DB is opened. IF the current date is beyond the forecast date, "due" should read "OVERDUE".
Changing forecastdate would obviously reset the "due" field to show how many days left to complete. If a date is entered into actualdate, then "due" should show COMPLETE.
Reports will be generated based on what is coming due, what is over due, and not show what is already complete.
I manage the production schedule of over 150 reports. Some of which are due daily, weekly, bi-weekly & monthly. (some reports have multiple due dates for the current month.) I have the production schedule in a table with multiple DueDate columns: DueDate1, DueDate2, DueDate3, etc. Some reports are due every business day of the week. I need to:
1.) Display the due dates (without having to enter them manually) so I can perform calculations, conditional formatting, etc.
2.) Should I display a column for each due date (31 columns accross for those that are due daily?) or show a duplicate row for that report for each day the report is due?
3) is there a formula that can be written to autopopulate the duedate fields for every instance and every month? (Sales_Report due every Mon, Wed, Fri (5/13/13, 5/15/13, 5/17)
I am currently putting these dates in manually every month and it would be a pain to continue that given our reporting production schedule will only increase.
I have a form which calculates many fields in the form. However, the issue that I have is how to use a different field in the query than the field that is shown in the drop down combo box in the calculation of an expression.
I'm using a calculation on my form that subtracts one number from another and stores the difference between the two in a 3rd field. This works correctly but I want the difference to show 1 decimal place at all times.
So for instance if subtract 35.1 from 35.2 I get a difference of "0.1".
But if I subtract 35.0 from 36.0 I get a difference of "1".
The first case is fine but I need the difference to be "1.0" in the second case.
I've set the number of decimal places in the table from auto to 1 and changed the format on the form to a standard number with 1 decimal place. Neither of these show a difference of 1 as "1.0"
I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.
Can I get a formula that can work this out automatically?
I need to make a DBA that will have 2 tables. One Has general Infomation name, address, phone, ect. Another that will log each call made to that customer. The purpose is to log everytime I speak to a customer and be able to determine using a qury who I have not called in a while.
I have a table in my database for my customers. Sometime my customers change their address but I want to keep these changes in other table. How it is possible.
My customer searches hires a car between two dates - StartDate and EndDate. I want to build an expression that searches my database for all the customers currently using cars (using cars on the day of the search.) How can this be done?
I'm having problems creating a query for a database (which I am building as part of my university assignment).
The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.
I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.
In case it is needed here is a list of fields in each of my tables:
Customer: Customer ID (PK) Company Name Company Street Address Company Town Company Region Company Postcode
Order: Order ID (PK) Order Taken By Date (DD/MM/YYYY) ( =now() ) Customer ID (FK) Delivery Street Address Delivery Town Delivery Region Delivery Postcode
Order/Product: Order ID (CK) Product ID (CK) Quantity Ordered
Product: Product ID (PK) Product Description Product Cost
Any help would be appriciated. Thanks for reading.
My database records order details for analysis. I have created a form to monitor individual trends in orders placed by my customers. As we supply bespoke items each customer has a list of products which they alone purchase, i.e we do not supply stock items, each item is specifically for one customer (they can then buy the items on a regular basis). The products are assigned to a customer in the products table.
The Analysis form I have created displays the customers name and two graphs. The first graph shows total sales each month, this graph updates as I navigate through the records (i.e. a different graph is loaded for each customer). The form also contains another graph, this is where the problem lies! This graph shows orders of the individual products, controlled by a drop down list containing all the products we supply. I need to limit this list to only show those products relevant to the current customer select on the form. (The products are assigned to a customer in my products table). Currently I achieve this by using a query which when the form is opened asks me to enter a customer ID, the drop down list is then limited to the products supplied to that customer.
What I would like is for the drop down list to update as I navigate through the records. i.e. when customer A is displayed show all the products assigned to customer A, when customer B is displayed show all the products assigned to customer B.
I’ve attached a snapshot of the form to help explain what I mean.
Now I have setup one form where i enter each transaction details
Item details, customer details and transaction details
I would like create customer find function, which will check current records for that customer account no, if customer account does not exist then form will let me create a new customer.
can anyone please tell me how can i do that . thanks I tried using combo box, but shows me record but does not let me enter new customer account no.
Hi all I have an order entry database. I need to talk through this problem I have, if anyone would like to let me call them then pls pm me. Within the database there is an add ne w order & details form where, the functionallity behind this prolem will need to be explained to me in very shall we say easy terms.
I need the product price, to be different all customers. The product list is the same for all customers. When the customer name is chosen by the "user" in the order entry form the form automatically tallies up orders based on the costs to that specific customer
any ideas guys on the best way forward. Thanks for reading
I want to automate an email to each one with the information that is held in the table. Much like a mail merge.
The email would be in one field.T The subject would be: "Your item[ Item Name] has been shipped." The body of the email would be: "Thanks for buying [product] on [date of purchase] etc etc."
My only problem is that I'm shit at code so I wanted to know if I could do it with Macros.
I could do it through a combination of using max records: sending an email, then repeating the exercise until all of my records have been sent.
I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.
I have a table with the customer details in and another table with the purchases in.
I have been using the folowing criteria:
Not Between Date() And #01/05/2007#
But i think this still still shows customers who have actually bought more recent than 01/05/2007.
Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.
I need a query to pick the bones out of sales transactional data like this...
Code: CustomerName Currency A Smith GBP A Smith USD B Jones GBP B Jones GBP T Brown GBP T Brown AUD C Wong GBP C Wong GBP S Giles USD S Giles USD
From the above data, only A Smith & T Brown ...have made purchases in more than one (different) currency. How on earth do I trap that with a query? (or will it need a macro?)
If so, the table is called sales & the columns are as per the data above (CustomerName & Currency)...
