Queries :: Create Query To List Hires By Customer Age Group
Jan 24, 2014
I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?
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"
I also have a table that list the items and on that table the column ItemID is the key.I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:
I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.
tblVersions ------------ VersionID (PK) SOPID(FK) Version (integer) IssueDate (date)
I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.
One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:
Code: SELECT Last(tblTest.VersionID) AS LastOfVersionID FROM tblTest WHERE (((tblTest.IssueDate)<Date()-10)) GROUP BY tblTest.SOPID;
The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.
Query3
Code: LastOfVersionID 100 90 20 40 666 888 55 8
Update: Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)
I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.
I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.
SELECT * FROM TableName AS a WHERE Not Exists (SELECT * FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);
So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.
This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.
For example: First four letters of last name = zabo Check to see if those four letters exist in CustomerID returns negative then CustomerID assigned = zabo0001
Another example: First four letters of last name = zabo Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then CustomerID assigned = zabo0003
Hey guys, I have a question that I am hoping you can point me in the right direction on. Before I ask it, I am new to access. I have been looking at templates and reading and things on how to build a database. I have my tables setup, relationships I think are right and my form created. One of the things I am having trouble with is creating a drop down list to be able to pick a customer name for. I setup the combo box and have a customerID as the control source in the properties. I have the Row Source Type as table/query and the Row Source query setup so it takes the info from the customer table.
query:
field: customer.* name table: customer customer
I think I am doing this the same way the templates have the properties but for some reason it won't allow me to pick from the list. It keeps saying "control can't be edited. It's bound to unknown field 'CustomerID'".
I attached my database. I hope it is clear enough that you might see what I have done wrong. Thanks in advance.
Is there a way (via the form) to search for all the records that have the same customer name and have that list show up so the user can select the exact record s/he needs?
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.
How I can insert the email address from 'Newemail' into my existing customer list 'masterlist' i.e. contact email of ID 1 and 3 :confused: ? I would like to ignore ID 4-5 from the 'Newemail' table. And I would like to keep ID 4 from the 'masterlist' table.
When attempting to select a letter in a form copied from the Northwind's Customer Phone List Form, I am now getting an error message that says "The object doesn't contain the Automation object "RecordsetClone'". I never had that problem before. Appears to be something in the Option Group that has gone awry, but I cannot access the Visual Basics behind the Macro. Is there a way to suppress this message? It does not appear to be a problem in bringing up the sought after page. :confused: :confused: Please help!!
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.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
Hi I'm hoping someone can help me with this problem (the access problem.. not the other one ! :cool: ) I have a table of Products, each item belongs to a product group, I have a table of Customers, amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products... when I enter an item into the sales order form I would like the discount for that customer for that item(item group) to go into the discount box.. sounds easy if you say it fast... I hope someone can help me with this !
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
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)
Hi All a newbie here so any help will be appreciated,
sorry for the long post but trying to give you all the information you might need.
I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.
But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.
So if this will work in access they can all come for 9am
I don't mind creating a new database and adding the additional information, if that's what it would take.
My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.
Database details (Microsoft Access 2002 version)
Table Name = details Field name = ID (auto-generated) Field name = FirstName (text) Field name = Surname (text)
If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.
There will be approximately 90 to 125 names.
Thank you in advance for all your help in this matter
Product1 5 240 A Product1 7 19 B Product1 6 12 C Product2 96 0 A Product2 98 23 B Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:
SELECT Count([summary].Key) AS CountKey FROM Summary, lookup WHERE ([Region])) Between [Region 1] And [Region 2]));
I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.
Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.
