Customers Who Have Not Ordered During The Last Week Query

Dec 18, 2007

Hello 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.

View Replies


ADVERTISEMENT

Modules & VBA :: Chart To Show Total Sales Ordered By Customers - Graph Method?

Sep 10, 2013

I'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.

View 3 Replies View Related

Queries :: Show Total Ordered For Week By Fish For Single Customer?

Aug 19, 2014

I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.

View 2 Replies View Related

Query - Extracting Customers Between Two Dates

Jan 2, 2007

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 2 Replies View Related

Query Customers That Havent Purchased In The Last Six Months

Nov 15, 2007

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.

View 4 Replies View Related

Query To Find Which Customers Have Birthday Today

Aug 13, 2014

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.

View 5 Replies View Related

Queries :: Create Query To Filter Customers When It Is Time Of Their Monthly Payments

May 21, 2014

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 ??

View 1 Replies View Related

Reports :: Inserting Page Break To Show Week By Week Pages

Feb 11, 2015

I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.

The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.

I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.

View 8 Replies View Related

Are Primay Key Autonumber Fields Chronologicaly Ordered?

Jan 24, 2007

Rephrasing: I have a table that has a PK that is autonumber. As I add records, the PK number always increases, with the last entry always larger than the previous (but the PKs may not always contain the next number in the sequence, e.g. 2,4,6, instead of 2,3,4). I need to do a query on this table to get fields from the most recent record that was added.

So, If I do a sort on the PK field and get the record with the largest primary key, am I assured that this is always the most recently added field????

If not, how can I construct a query that will provide me with the most recently entered record?

Why I need to do this: I do a query on table 'A' and then append one record from A to table 'B'. Then, using the record I just copied, I do an append query on another table and then place those results in yet another table.

Or, is there another way to accomplish this?
Thanks
J

View 6 Replies View Related

Tables :: How To Break Down Repeated (Quantity Ordered) 1 Through 5

Apr 17, 2013

Right now I have a flat database with too many unnormalized fields. I am trying to figure out how to break down the repeated "QuantityOrdered" 1 through 5. "QuantityReceived" 1 through 5.

"RequestedItems" 1 through 5 and "Price" per unit 1 through 5. I'm not sure if they go into the same tables, if they are each listed 5 times and if in different tables how many instances 1 or 5 to show each as seperate and how to create a relationship between them.

View 4 Replies View Related

General :: Creating Unique Ordered Number Field With Auto Shifting Values

May 31, 2013

I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously.

Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries.

Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3.

View 4 Replies View Related

Week Commencing Day From Week Number

Nov 6, 2006

I have a query that groups records by date into week numbers using the following expression.

WeekNumber: DatePart("ww",[Date],2)

I would prefer to display the result as the week commencing Monday date.

How?

View 13 Replies View Related

Query By Week Beginning

Sep 6, 2007

I am trying to generate a Query by week always beginning on a Monday. I pick any date....and want all records in both printed version and chart version i,e, number of safety meetings attended vs number who should have attended for this week. I can enter data with todays date, well any day this week. Then at some future date ask for who attended this week and percentages (another story). Any ideas?

Nav4

View 1 Replies View Related

Can You Query By Week Number Rather Than Between Dates

May 17, 2005

Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.

View 5 Replies View Related

How Do I Group Query Results By Week?

Jul 21, 2005

Hi everyone

I need to run a query that returns the number of bookings made for each week so far this year. However the date is held in DD/MM/YY format. I had a similar query that worked fine for monthly grouping using Month([Start Date])AS FilterMonth but tried replacing month with week, this didn't work.

Any ideas?

Thanks! :)

View 1 Replies View Related

Fiscal Week And Payperiod Query

Apr 24, 2008

I have a database that tracks call-monitoring scores for call center employees.
They are given a bonus based on their scores which is calculated every pay-period.
The following attachment is what I need the query to show. Or perhaps an excel document linked to the database.
Just wondering if you all could take a look at this and tell me what the easiest method would be.
Also, what would be involved.
My major concern is the query being able to differentiate the different fiscal weeks and or payperiods.

View 1 Replies View Related

Current Week Query In 2 Date Feilds

Nov 17, 2006

I have a query with below formular which return's all records between Sunday and Saturday

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week.
I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time.
What am I doing wrong ???

Tks

View 1 Replies View Related

Record Missing Using Work Week Query

Feb 25, 2007

I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

View 14 Replies View Related

Correct Week Number Format In A Query

Jan 6, 2005

Wondering if anyone can advise...

The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:

WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)

However my problem now is that I need to replicate this format in a Query within Access. The formula

WeekNo: Format(Date(),"ww")

treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?

There's a challenge for someone!! Help appreciated.

View 1 Replies View Related

Queries :: Week Number Query When Year Changes

Feb 28, 2014

I analyse things by the date they are ordered by the office.

The date gets put into a spreadsheet and i import this into access for the querying.

I need to summarise it by weeknumber and year.

I use the following queries:

To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]

The problem is when the year changes.

The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.

Therefore when the year and week number is put together the following results are given for that particular week:

DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1

All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.

How can I amend my query so that the above all show the correct year and week number: 2014_1 ?

View 9 Replies View Related

Reports :: Query To Show Only Current Week

Dec 3, 2014

How can i make a conditional report that can give me some data from the table.

View 1 Replies View Related

Query Criteria To Display Current Week Only?

Jan 23, 2012

I have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.

So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.

So I tried this and got nowhere:

Between #(Date())# AND #(Date()+7)#

View 4 Replies View Related

Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)

May 31, 2007

Hi

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.:D

Thanks
Daniel

View 6 Replies View Related

Queries :: Table Of End Of Week Sales - Query Where There Is No Match

May 4, 2014

I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.

What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.

tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;

My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;

The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.

So that when I do year on year revenue calculations it works properly.

View 6 Replies View Related

When The Last Week Of The Year And The First Week Of The Next, Are The Same!

Mar 18, 2006

I have the following expression which works nicely to group all of my dates into their respective weeks of the year, by week dates beginning Mondays:

WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.

Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.

How can I adjust my code to get one total group of dates for the week of 12/26/05?

Help greatly-needed!....

View 1 Replies View Related

Creating A Call Log For Customers?

Mar 6, 2013

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 Related







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