New to ACCESS

[Guest]

I downloaded the "Order Entry" database from Microsoft web site and I am
making the need changes to fit our company needs. How can I get a
query,report or other to give me all of the orders for the past 30 days on
each individual customerID. I need to be able to enter the customer ID and
print out all they have ordered in the past month for that ID, or can ACCESS
print out a past month orders for each individual customerID. Can I call up
each customerID from a form and see all that ID had gotten in the past month.
I need to be able to show the businesses all the item they had ordered in the
past month and their payments and credits (returns). I know I am asking alot
from you all but we have a small business we are trying to upgrade from the
old notebook and pen method.

 

[Guest]

Hi

Sorry but I don't think it can be done. Please don't misunderstand most of
the people on this forum would be able to change the database you have and
enable all the options you want very quickly - so "access" can do it. BUT I
feel that it may be that you need some training before "you" can do it.

Access it not like word or excell where you can just turn them on and start
using the programmes right away and see what happens and you will eventually
figure it out.

With access you need to start from the bigining and work up. There is a
steep learning line. It will not take you long so don't worry. The basics
can be got quite quickly by most people - BUT ("very" important) - there
should be at least one person in your company who not only knows what the
users are doing but who also fully understands "how" it is working. 1+1=2
and any users can enter the digit 1 into a text box and another 1 into
another but there must be someone in the company who understands that this is
a number (not text) and what this means to access. Also this person must be
able to tell access they want 2 to appearing somewhere (and why this number 2
should not be stored) they also need to tell access what this number 2 should
do (if anything) after it is created.

Sorry if this is not what you want to hear. You will find that access is
not too difficult to pick up if you start from the basics, but simply
downloading a databse and starting to use it is really not a good idea.

 

[Keith Wilby]

I downloaded the "Order Entry" database from Microsoft web site and I am
making the need changes to fit our company needs. How can I get a
query,report or other to give me all of the orders for the past 30 days on
each individual customerID. I need to be able to enter the customer ID and
print out all they have ordered in the past month for that ID, or can
ACCESS
print out a past month orders for each individual customerID. Can I call
up
each customerID from a form and see all that ID had gotten in the past
month.
I need to be able to show the businesses all the item they had ordered in
the
past month and their payments and credits (returns). I know I am asking
alot
from you all but we have a small business we are trying to upgrade from
the
old notebook and pen method.

You need to locate the appropriate date field in the appropriate query and
enter this as the filter criteria:

Between Date()-30 And Date()

Strictly speaking not the last month but the last 30 days which is pretty
close.

Keith.
www.keithwilby.com

 

[onedaywhen]

Access it not like word or excell where you can just turn them on and start
using the programmes right away and see what happens and you will eventually
figure it out.

Dear OP, Don't be too put off by Wayne of Manchester. Downloading an
example and trying to modify it is valid approach. Temporal data is
tricky in SQL and I agree with Wayne about starting from basics so I
urge you to take one of the many quick and free online SQL courses
(e.g. http://www.sqlcourse.com/). Do lots of practice and before too
long you'll be able to spot all the flaws in the example application
you downloaded <g>.

Jamie.

--

 

[Guest]

I am reading "ACCESS 2003 Inside Out" and taking the e-learing from Microsoft
course 4100 for ACCESS. I will also look into the course mentioned below. I
thank you for your time, I will be upgrading our Office programming to Office
2007 sometime before the end of the month. Once again thank you for your time
and I will return to get help as needed.

 

[Guest]

if you need to pull records out by customer for the "current" month, do this:

1. create a query with the tables you require
2. create TWO calculated fields as shown below:

orderMonth: Month([Orders]![OrderDate])
orderYear: Year([Orders]![OrderDate])

(replace ORDERS with your actual table name and ORDERDATE with your actual
date field.)

the first calculated field extracts the MONTH portion of your order date,
the second extracts the YEAR portion (i.e. for an order made on 12 Mar 2007,
orderMonth returns a value of 3 and orderYear returns a value of 2007)

in your querys criteria line, under "orderMonth", enter the value...

Month(date())

....this will extract the Month portion of todays date (e.g. run it today it
will return 3 for march, run the same query in july, it will return 7...you
never have to amend this when you run the query!!!)

in the querys criteria line under "orderYear" enter...

Year(date())

.....this extracts the year portion of todays date (e.g. 2007, run the same
query in five years time it will return 2012, again you don't have to make
any changes to it...the "date()" function in the calculated field takes care
of that by returning your system date).

keep both criteria in the same line, so that the query reads "select ALL
records from the orders table where the Month part of the order date is
equal to the Month part of todays date AND the year part of the order date is
equal to the year part of todays date.

the above query will return ALL records where a sale has been made in the
"current month" (e.g if you run it today, it will show all orders since 1
March 2007. if you run it in three weeks time it will show all orders/sales
since 1 apr). if you want to limit the records to a single customer, just
enter the customer id number in the same line on the criteria field under the
customer id field.

the equivelant sql statement looks like this:

SELECT Customers.CustomerID, Orders.OrderID, Orders.OrderDate,
Month([Orders]![OrderDate]) AS orderMonth, Year([Orders]![OrderDate]) AS
orderYear
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Month([Orders]![OrderDate]))=Month(Date())) AND
((Year([Orders]![OrderDate]))=Year(Date())))
ORDER BY Customers.CustomerID;


Now, you have a query where you will NEVER have to amend the dates, the
query will always determine the "current month" based on the calculated
fields, so that on 1 April, the query will no longer pick up sales from March
and so on. all you have to change, is the customer id number


jimaldo

 

[Guest]

if you're only looking to get records within the last 30 days, in your
criteria line under your order date field, type

=Date()-30

again, you won't ever have to amend this, it will always be 30 days previous
to the current date. what you need to bear in mind though is, "past month"
and "past 30 days" are completely different things. what criteria would you
use, say, if you ran the query on 31May, or 28 February?

 

[Guest]

if you're only looking to get records within the last 30 days, in your
criteria line under your order date field, type

=Date()-30

again, you won't ever have to amend this, it will always be 30 days previous
to the current date. what you need to bear in mind though is, "past month"
and "past 30 days" are completely different things. what criteria would you
use, say, if you ran the query on 31May, or 28 February?

jimaldo

 

[Guest]

if you're only looking to get records within the last 30 days, in your
criteria line under your order date field, type

=Date()-30

again, you won't ever have to amend this, it will always be 30 days previous
to the current date. what you need to bear in mind though is, "past month"
and "past 30 days" are completely different things. what criteria would you
use, say, if you ran the query on 31May, or 28 February?

jimaldo