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?
View RepliesHello all, I'm new to this forum.
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.
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.
Query which I want to create about finding the date of birth of the customer.
I explain:
I have a table (tblCustomers) which among others contains the field "DateOfBirth". I would like to create a query so that looking into tblCustomer about the dates where the month and day of birth of the client is the same as the Date () so that I know every day which customers have birthdays.
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are
1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)
I made another field called "states". In this field I putted the following expression
Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")
Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.
To overcome the above shortcomings, i modified the expression to
Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")
This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??
SELECT tblLeaveBalance_Cashables1.Facility, tblLeaveBalance_Cashables1.Division, tblLeaveBalance_Cashables1.Position, tblLeaveBalance_Cashables1.Name, tblLeaveBalance_Cashables1.[Class Code], tblLeaveBalance_Cashables1.Class_Title AS Classification, tblLeaveBalance_Cashables1.CBID, tblLeaveBalance_Cashables1.[SS Months], tblLeaveBalance_Cashables1.Age,
[code]....
I need to extract data that where vacation time is >640 or annual leave is >640 and where personal Hol hours are >24 and CBID is M06,S06 and R06.
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
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.
View 3 Replies View RelatedI have a table for my customers with current information.
CustomerID:0001
CustomerName: xxxxx
Address:xxxxxx
Telephone :xxxxxx
etc.....
But some time customers change their information. I want to keep their old information in other table. How it is possible?
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.
View 1 Replies View RelatedMy 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.
Thanks for any suggestions.
Hi,
I have 3 tables
Customers
Tranasaction
Items
Linked togather, working fine.
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.
thanks in advance : )
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
Mark
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?
I have a table with all of the relevant data.
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 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)...
I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.
The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.
I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?
But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.
tblCustomers [CustomerNumber] primary key
tblCustomerOrders [OrderNumber] primary key
[CustomerNumber] linking to tblCustomers
So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.
I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.
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.
View 3 Replies View RelatedI have a database that I will use for invoicing, but I would like it to automatically create an invoice for customers based on parameters set for that customer (e.g., monthly, biweekly, etc.). I have tables containing the customer information, the item they are being billing, the price, etc. I want to be able to have access automatically create the invoices and add them onto the invoice table each month.
Maybe there is a better way, but I thought that if I created a query for all people that are billed biweekly and all people that are billed monthly, that I could run the queries when applicable and then somehow write a macro that would go through the list of customers and add each of them to the Invoice table and add an autonumber. That way I could click run query, run macro, and then do my invoicing. I don't know if that is the way to go or not.
Hi,
Well I know why this happens b/c the table that the information is being pulled is the subsequently being updated however when moving to a diff customer or closing the form etc.. The user is then displayed the MsgBox of Write Conflict and supplying the user with 3 options of Disregarding Changes, Saving changes to Clipboard or Save Changes.
Baisically is their a way to restrict this write conflict msgbox and have it so that it saves the users changes every time.
cheers
monkey o_0
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 wanted to auto populate an invoice record with same values as previously ordered by that same customer. I don't want to use default values as every customer orders different things.
BUT almost all customers make REPEAT orders of exactly the same things they ordered previously.
So...for example, can I search the invoice records by customer ID/Name and copy all the data from their previous into a new invoice record. I guess I could do this with an append query?
But will an append query run when I just click on 'new record' in the invoice form? Maybe there is a property '.on new record'...open append query....?
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 RelatedI've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
I've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.
What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(
Help!
Hi,
I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.
Any ideas on how I achieve this through an Access query?
Many thanks
Clay