General :: Finding Customer Who Spent The Most

Sep 2, 2014

I am working on a database at the moment to try and find the customer that has spent the most money. At the moment i can only get the customers that have placed the most orders. I have a dispatch table that consists of all the orders and a customer table that are linked using a Customer Number. Each order has a dispatch number because one customer can buy more than one product per order. Like i said at the start I'm trying to find the customer that has spent the most money but the top 10 would be better.

View Replies


ADVERTISEMENT

General :: Create Check On A Field (customer ID) Of Customer Table In MS Access?

Sep 7, 2012

create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"

View 6 Replies View Related

General :: How To Get Total Spent Hours In Office For The Employee

Jan 28, 2014

I am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.

I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem.

SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] 60 & Format([Minutes] Mod 60, ":00")
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #01/20/2014# and #01/24/2014#
GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;

View 3 Replies View Related

Add Customer Name Into Table Each Time Unique Customer Added To SaleTable

Dec 11, 2013

I have a Table Sales - that lists customer and order information

Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale

(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.

So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)

View 6 Replies View Related

General :: Customer Text Box Validator

Mar 15, 2014

I have 2 controls, a textbox and combo box. I want to prevent users from entering anything other than number integers on the textbox and only strings on the combo box.I want codes that will prevent users by customized msgbox instead of default access error message. I am using ms Access 2010.

View 2 Replies View Related

General :: How To Link Customer To Postcode Table

Mar 6, 2014

I am using access 2010 and have the following question?

I have 2 tables, 1 called UKFile and the other Customers with the following fields

[UKFILE] THIS IS WHERE ADDRESS DETAILS ARE STORED
POSTCODEID
POSTCODE
STREET
TOWN

[CUSTOMERS]
CUSTOMERNAME
POSTCODE
STREET
TOWN
CONTACTNAME
TELEPHONE

How do I link these so that when I enter the customers postcode it grabs the data from the UKFile table.

Should I change structure at this early stage,

View 2 Replies View Related

General :: Auto Generate Unique Customer IDs

Aug 16, 2012

I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.

The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;

=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.

View 14 Replies View Related

Combining Customer And Customer Contact Tables

Dec 24, 2011

I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.

View 1 Replies View Related

General :: Remove Customer From List But Keep All Related Info

Sep 26, 2012

Let's say I have a list of customers. For each customer I have much more info on other lists (order list, personal info list, bank info list, and so on) - all are of course connected properly.

Now let's say a certain customer is no longer my customer, so I want to remove him from the customer-list. But, I want to move him to a different list - past-customers - so all the information that was related to that customer will remain so. In short, I want to remove from the customer-list without affecting the related data.

View 4 Replies View Related

Budget Less Spent

Dec 9, 2005

I am using Access 2003 and am in the process of building a database for the process of recording invoices there are three properties one of which has three schedules.

I have created a query that has budget amounts.

Another query that brings up the amount spent by code.

What I want to do is have the budget figures with the amount spent by code beside it, I can do this but it does not include any items which have zero spent in them so far.

Any pointers regardless of how simple they sound would be gratefully recieved I am newish to building databases.

Thanks

View 2 Replies View Related

General :: Dynamically Search Multiple Fields Using Customer Data?

Feb 19, 2014

"Dynamically search multiple fields" using my own customer data.

I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.

Right now the data is in excel so I have a completely blank page to fill.

View 2 Replies View Related

General :: Create Invoice Per Month Of Multiple Orders Per Customer?

Mar 19, 2013

My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.

View 1 Replies View Related

General :: Use Drop Down Control In Service Order To Select Customer

Mar 1, 2014

I have a client table.
I have a client product table for ski's
Each Client has 1 or more products (skis)

I have a Service Order table and form that I use a drop down control in the Service order to select the customer.In the Service Order Form a I have a continuous Subform for detailed service.

This is where I'm having the problem.In the detail subform I want to select from a drop down box the customers particular ski that I want to service. how to have only that one customers ski's to show up.

View 14 Replies View Related

Record Time Spent Task?

Jul 9, 2014

I am using the Issues Template Database and would like to add a function to it.

I would like to record each time spent task/ action when working on an issue.

For example:
Tasks
- Email
- Phone Call
- Report

So, say I wanted to add one Report- time taken 30mins and record that then on an another day, Email- 10mins So record each task and time taken.

View 1 Replies View Related

Working Out Total Time Spent (In A Query)

Apr 8, 2008

In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.

I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID

This lists all the time open's and timeclosed's for that support ticket.

how can I work out the total time spent on that support ticket?

View 5 Replies View Related

General :: Finding Mouse Coordinates

Mar 27, 2015

I have an Access 2007 continuous view Form and would like to capture the mouse X=-Y coordinates to set the start up position for a small Form.

I hoped to use MouseMove method, but this does not work.

View 14 Replies View Related

General :: Finding Last Used Row In Excel Sheet?

Nov 15, 2013

I have the following code which returns me the number of rows in an excel sheet:-

Lastrows = .Worksheets(1).Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row

How do I mod the code to find the last 'used' row in the sheet?

The sheet is automatically generated daily, so I can't change anything in that area. It's always 2000 rows long and usually only has around 1100-1200 rows of data.

It's just that I do a for loop later on in my code for 1 to Lastrows and would like it as exact as I can.

View 4 Replies View Related

General :: Finding Average Amount Of Items

Feb 18, 2013

I work in the Insurance Industry and I am having a hard time trying to find the formula for finding the answer to the below issue I am having.

In a table I have 4 columns:

1st: Total # of Claims (Claim Count)
2nd: Total # of Items (Item Count)
3rd: Item % of Claim Count
4th: Average # of Items per Claim (This is the number which I am trying to get)

Is there a formula which could give me the "Average # of Items per Claim"?

View 3 Replies View Related

Queries :: Tracking Time Spent On Customers Records Based On Date Of Entry

Apr 17, 2014

I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.

View 3 Replies View Related

General :: Access 2007 / Finding A Related Record That Has 2 Foreign Keys?

Jan 22, 2013

I am using access 2007

i have a materialsUsed tbl that list all the materials used in a job and the quantity of each.

(ID
Material Code
Quantity
Unit of Measure)

I also have 2 look-up tables - 1 for the material codes and 1 for the units of measure

I have another table, priceperunitofMeasure

id
material Code (FK to material lookup tbl)
Unit of Measure (FK to unit of measure tbl)
Price

In my form Users will select material code from combo box - enter the quantity, and select a unit of measure from a combo box.

For example: they could enter the following :

Paint 2 gallons
Paint 3 tubes
Paint 1 pint

How do I lookup the price per unit of measure for each of these records in the price per unit of meassure table? The price per unit of measure table has 2 foreign keys.

View 4 Replies View Related

Customer Db

Sep 3, 2005

Hi,

I'm creating a db for customers and each customer can have many service orders.

In the customer form I have two buttons, one to create a new service order and one to open existing service orders.

Both buttons work as they are supposed to although I am doing this with custid, so both the customer and serviceorder tables have a field called custid and when the button is clicked to show existing service orders it matches both custid's and shows the corresponding records. However it onlt works at the moment with me manually entering the custid in the field in the serviceorder table. When a new service order is created I need the custid to be entered in both tables.

I hope this makes some sense, any help is grately appreciated!!

View 6 Replies View Related

Customer History

Nov 1, 2005

Hi,

I currently have a "customer" form (which displays client details from a table - name, address etc) I would like to add history comments for each customer.

Does anyone know how i would go about doing this.

I image i could press a button on the current "customer" form that will open a history form which after typing a comment would be displayed on the main "customer" form, with date, time and comment.
I would obviously be able to add lots of comments to each record.

i would be greatful for any kind of help

Regards
Chris

View 4 Replies View Related

Customer & Supplier - What If They Are Both?

Aug 14, 2006

Currently my design consists of a table for customers and a table for suppliers. At first it seems logical. Or so I thought!

As my design is evloving I have noticed a problem. What if a supplier calls and says "I want to purchase a..." they then also become a customer!

Obvoius I know but do I leave the design (attached) as is or disolve the supplier table and then just add one field called [CustSupp] and have three options to choose from "Customer", "Supplier" or "Both"

I could then base any other forms like the [FrmCustomers] on a query that looks at the [CustSupp] field and has a Like "Customer" criteria

Whst is the best method. Any advice greatly received.

View 12 Replies View Related

Using 2 Customer Ids In One Table

Nov 27, 2006

i cant do this.. its too long..

basically i have a job table and a person table..

jobid, job date, job details....

personid, personname, person details...

the job table needs to store who BOOKED the job, and who was the PASSENGER in the job.

i want to use the person ids in the job table..

im having trouble here with the whole relationships and forms..

i need my user to be able to simultaneously add a booker and a customer to the person table and assign them both to a job and i dont know the best way to do it..

a few rules

job1 can be
booked by person 1
have passenger as person 1

job 1 can be
booked by person 1
have passenger as person 2

also the booker and/or passenger may be unknown.. which is why i want to create an unknown person in the person table.. with id 1 for example.. and anytime the booker or passenger isnt known my user can just type in id 1


someone help me.

View 10 Replies View Related

Customer Database

Jan 21, 2008

hi all,

first time posting so please excuse the newbness.

i need to create a customer database, which i would like to be able to generate invoices from, nothing fancy or complicated, just name, address phone number etc. with a button to put this detail into a template word .doc file.

i would also like to be able to import all names and addresses at the end of the year to send a christmas card maybe or promotional e-mail.

how difficult is this to do? i kinda remember doing something similar at school years ago but can't remember how i did it.

any help/information would be greatly appreciated.

thanks in advance

View 2 Replies View Related

Top 5 Products Per Customer

Jan 22, 2008

Hi There,
I have created this query and i want to return the top 5 suppliers per LOB. It is returning something but it is not the top 5. I want to limit only to return the top 5. Pls. help and thanks. See attached file.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved