Query to find Increases in Amount for Consecutive dates

[PraizesLiberato]

Hi Everyone,
This is my table and Im tring to write a query that will give me all the Names as of a particular date Ex:22-02-2010. and with it give me the increase in the ammount of that particular as of the previous day. i going nuts using the query builder. please helppp!!!!

Really need this today.

PKid - AutoNumber
NAME - Text
Amount - Number
Dateval - Date/Time

 

[PNGBill]

I suggest you do a simple query of all fields with no criteria etc

Queries, New, Design View and select the table. You will then be given a list of fields to click on and drag or double click to get them into your query design.

Run the query and see what the result is.

You may have a problem with your date field in that it also has time.

Once you have the result you can start to filter them (criteria) and or add new fields to add values to them.

See if you can get the simple select query working first.

 

[PraizesLiberato]

I suggest you do a simple query of all fields with no criteria etc

Queries, New, Design View and select the table. You will then be given a list of fields to click on and drag or double click to get them into your query design.

Run the query and see what the result is.

You may have a problem with your date field in that it also has time.

Once you have the result you can start to filter them (criteria) and or add new fields to add values to them.

See if you can get the simple select query working first.

Okay. HMMMMMM....

 

[PraizesLiberato]

Okay, See I have written a query but it only works for one instance or a particular name that i have to query for like seen below (I have to Query exactly Jacobs name to Get all the ammount for jacob that has increased.)

But i want to go one step further, and query all the names for a particular date like 22-01-2010 and get all the names and their increases from the previous day that is 21-01-2010.

SELECT ABC.NAME, (SELECT [abc].[Amount] FROM [abc] where [abc].[Dateval]=#21/01/2010# and [abc].[Name]='Jacob')-[abc].[Amount] AS Increase, ABC.Dateval, ABC.Amount
FROM ABC
WHERE (((ABC.NAME)='Jacob') AND ((ABC.Dateval)=#1/22/2010#) AND ((ABC.Amount)>(SELECT [abc].[Amount] FROM [abc] where [abc].[Dateval]=#21/01/2010# and [abc].[Name]='Jacob')));

i have added an attachment to make this more simple. if you have a solution it would be gr8.

 

[PNGBill]

When you query you can either have the query use the record name or ask for the name.

If you use query design, you would type in the criteria field for Name [Enter Name] and as the query runs it asks for the name.
The same goes for dates.
You can use date criteria by Date() -1 or Date() 1 for tomorrow or yesterday or if you Dateval: [Enter date] the query will ask for the date to be entered.
You can also Dateval: Between[Enter first date]and[Enter Last date].

Just looking in my sql book on how to do it in sql.

Once you get this sorted the query will return all the instances of Jacob for the selected dates.

If this query was called from a form then the wizard that sets up the command button will allow you to link the name on the form to the name on the query - don't use criteria here as you want all names selected in the query and the onclick will filter the names for you.

 

[PraizesLiberato]

When you query you can either have the query use the record name or ask for the name.

If you use query design, you would type in the criteria field for Name [Enter Name] and as the query runs it asks for the name.
The same goes for dates.
You can use date criteria by Date() -1 or Date() 1 for tomorrow or yesterday or if you Dateval: [Enter date] the query will ask for the date to be entered.
You can also Dateval: Between[Enter first date]and[Enter Last date].

Just looking in my sql book on how to do it in sql.

Once you get this sorted the query will return all the instances of Jacob for the selected dates.

If this query was called from a form then the wizard that sets up the command button will allow you to link the name on the form to the name on the query - don't use criteria here as you want all names selected in the query and the onclick will filter the names for you.

i understand what you are saying buddy but i want to get the increases for a particular day so i can display all the increases only. If there were 100 or thousand different names how can the user go through each to find the increases a of a particular day.

You kno what im saying.

Im trying to run a query that will show all the increases only as soon as the user chooses a date. im still tring but this will take some time. if you have a solution would really appreciate it.

 

[PNGBill]

This will output all data for the two selected dates.

SELECT Testtbl.PKid, Testtbl.Name, Testtbl.Amount, Testtbl.Dateval
FROM Testtbl
WHERE (((Testtbl.Dateval)=#1/1/2010#)) OR (((Testtbl.Dateval)=#1/2/2010#));

 

[PNGBill]

And this should give you a crosstab result

TRANSFORM Sum(Testtbl.Amount) AS SumOfAmount
SELECT Testtbl.Name
FROM Testtbl
WHERE (((Testtbl.Dateval)=#1/1/2010#)) OR (((Testtbl.Dateval)=#1/2/2010#))
GROUP BY Testtbl.PKid, Testtbl.Name
PIVOT Testtbl.Dateval;

 

[PNGBill]

This will give you Name, firstdate, amount, seconddate, amount

SELECT QryTest2.Name, QryTest2.FirstAmount, QryTest2.Firstdate, QryTest3.SecoundAmount, QryTest3.Seconddate
FROM QryTest2 INNER JOIN QryTest3 ON QryTest2.Name = QryTest3.Name;

 

[PNGBill]

Sorry...

1st query:
SELECT Testtbl.Name, Testtbl.Amount AS FirstAmount, Testtbl.Dateval AS Firstdate
FROM Testtbl
WHERE (((Testtbl.Dateval)=[Enter 1st Date]));

2nd query:
SELECT Testtbl.Name, Testtbl.Amount AS SecoundAmount, Testtbl.Dateval AS Seconddate
FROM Testtbl
WHERE (((Testtbl.Dateval)=[Enter 2nd Date Date]));

and 3rd query
SELECT QryTest2.Name, QryTest2.FirstAmount, QryTest2.Firstdate, QryTest3.SecoundAmount, QryTest3.Seconddate
FROM QryTest2 INNER JOIN QryTest3 ON QryTest2.Name = QryTest3.Name;

The queries could be done as sub queries but I just wanted to get a result for now.

Trust this assists

 

[PraizesLiberato]

Sorry...

1st query:
SELECT Testtbl.Name, Testtbl.Amount AS FirstAmount, Testtbl.Dateval AS Firstdate
FROM Testtbl
WHERE (((Testtbl.Dateval)=[Enter 1st Date]));

2nd query:
SELECT Testtbl.Name, Testtbl.Amount AS SecoundAmount, Testtbl.Dateval AS Seconddate
FROM Testtbl
WHERE (((Testtbl.Dateval)=[Enter 2nd Date Date]));

and 3rd query
SELECT QryTest2.Name, QryTest2.FirstAmount, QryTest2.Firstdate, QryTest3.SecoundAmount, QryTest3.Seconddate
FROM QryTest2 INNER JOIN QryTest3 ON QryTest2.Name = QryTest3.Name;

The queries could be done as sub queries but I just wanted to get a result for now.

Trust this assists

Dude that was awesome. I'll probable need to use this same thing for some other work also. Thanks a bunch..I guess all ineed is to just find the difference at this point 4 the 2 dates and use a criteria to filter out everthing except for the Increases.

Awesome Bro. Thanks. God Bless

 

[PNGBill]

I just did the query in design view and then viewed it in sql and wala!!

Big problem was make sure you do not use the primary key as you need to join on name and not pk as pk numbers are unique therefore you get no results.

You know how to add another field to the query and use DateDiff or some other function to give you a result for the diff between two or more fields in your query??

 

[PraizesLiberato]

True. This solution will be helpful in analysing data as well. thanks. Also if you know any books that teach on such complex queries and methods just lemme know. thanks.

Im sure i need a lot more in the coming months. Thanks 4 the help bro.

 

[PNGBill]

Always try the queries as simple one first and then make more complicated as you. This we, you find out where it stops working and have a better idea on what to study and or ask for assistance.

If you work in sql, great, otherwise I suggest you use query design and you can always convert it to sql like i id with one click of the mouse.

Sometimes using a wizard to do a query may make it difficult to find why it doesn't work when it all looks like a dogs breakfast.

Good luck