I am trying to create a report which will show the last twelve weeks of orders by client. In the table I have an Order Date and of course the bill to clients. I tried to create a Crosstab query for the wizard and can get month by month but need the last twelve weeks, week by week. Is there a way to do this?
View RepliesHello,
I am putting together a database for a charity and am having some problems. At present I am working seven days a week and my time for research is so limited that I can only ask here for advice since I have neither the time nor resources to seek it out myself.
We do mail shots to various trusts and businesses to appeal for donations. At present we have two large, bloated MS Works Databases (one for Trusts and one for businesses) that contain names, addresses and so on. I have taken on the task to make our mail shoots more efficient. My vision is that the majority of time should be spent searching out new business/trusts and organising events while actually doing the mail shoots should basically be done at the touch of a button (it won't quite work like that, but previously the actual mailshots took up about 80% of the time. I want to drive that number down)
Having no Access experiance whatsoever I set about creating a Database with twelve tables, one for each month. Each month would contain a list of places that we would "hit"
I then came across a problem. When I am searching out new addresses I would have to cross reference them on 12 different tables. This struck me as being impractical but at the time of creation I didn't have the foresight to create a masterlist. Although I do have the list on my Works DB, it would be useless as time passed since no more entries would be inputted on there.
I was hoping someone could tell me how I can put together a masterlist using the data I already have and maybe if possible setup the 12 tables in such a way that by inputting an entry on one of them will automatically crossover the name of the business to the masterlist without me having to enter it twice.
I realise this may sound confusing, and if i had more time I would research the answer myself but I am stretched to the limit and know too little about this application. I did try to take on a Lyndia (sp?) course but again I just don't have the time. If you have any questions, I will check back later to answer them.
I am using Access 2002 (but my DB is in Access 2000 format - so it says)
Thanks kindly.
I have a database that I am using to report metrics based on seasonally available data. What I am running into is the problem that the seasonally entered data doesn't start on the same date every year.
An example:
I have data that was entered into my database last year starting July 26, 2004. This is considered part of week 1 of my season and I want to compare that data to records that started being entered on August 15, 2005. Which is week 1 of the 2005 season.
Records include the following information: id, date, other irrelevant details
Does anyone have any ideas on how I might be able to accomplish this? Thanks in advance for your help!
Hi there,
Was there a recent update or something that may have affected a Union Query in MS Access 2003 (operating system XP Pro, all current updates installed)?
In our database we have a union query that display available sessions and time period of availability, this is done by identifying the session with **available** at the start of each line for some reason it works on some computers and not others.
Does anyone know anything that may effect this code in operating 100% of the time?
thanks in advance.. Colin
The SQL code used is a follows:-
SELECT '** available **','', '',format(A.session_end, "dd/mm/yyyy") as START_DATE,format(A.session_end,"hh:nn") as START_TIME, format(B.session_start, "HH:MM") AS END_TIME, int(DateDiff("n",a.session_end,b.session_start) / 60) AS Expr1, (DateDiff("n",a.session_end,b.session_start) - expr1 * 60) AS Expr2,a.session_start
FROM session_last AS A, session_last AS B
WHERE (((B.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])) And (B.session_start)=(SELECT MIN(C.session_start)
FROM session_last C
WHERE C.simulator_code = A.simulator_code
AND C.session_start BETWEEN (([Forms]![Bookings]![StartDateInput])) AND (([Forms]![Bookings]![EndDateInput]))
AND C.session_start > A.session_start
)) AND (((A.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo])) AND ((A.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])))
AND ((B.simulator_code)=[A].[simulator_code]) AND (([B].[session_start]-[A].[session_end])<>0))
UNION SELECT '',C.customer_code, C.session_type, format(C.session_start, "dd/mm/yyyy"), format(C.session_start,"HH:MM"), format(C.session_end,"HH:MM"), int(DateDiff("n", C.session_start, C.session_end) / 60) AS Expr3, (DateDiff("n", C.session_start, C.session_end) - expr3 * 60) AS Expr4,c.session_start
FROM session_last AS C
WHERE (C.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput]))
AND (((C.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo]))
ORDER BY 9, 4, 5, 1;
END of SQL code..
My main query has a list of events with the corresponding dates that they occured.
I want to create a second query that shows Monday’s date of the current week and the Monday date of the preceding 51 weeks.
How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.
Then I want to create a third query by combining the first two such that the with the dates and events main query are displayed and grouped by the Monday dates for the year query.
2nd QryMain Query
MondaysEvent #Date
8/6/2007789328/6/2007
8/13/2007
8/20/2007
8/27/2007
9/3/2007
9/10/2007
9/17/2007
9/24/2007
10/1/2007
10/8/20078089910/8/2007
10/15/2007
10/22/2007
10/29/20078179111/1/2007
11/5/20078199111/8/2007
11/12/2007
11/19/2007
11/26/2007
12/3/20078269712/5/2007
12/3/20078274812/6/2007
12/3/20078278612/7/2007
12/10/2007
12/17/20078311112/18/2007
12/24/2007
12/31/2007835531/4/2008
12/31/2007835611/4/2008
Hi,
I have created a basic query, in MS 1997
Would someone be kind enough to give me the code for the following,
I need to display only the last 13 weeks worth of dates in this query.
The table is called "Tbl_History_RotAwaitShip"
The field is called "Time/Date" and is formatted as "13/05/2005"
Thankyou
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
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access?
This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like,
"Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
So I have a project where I have to get employees to use infopath to fill out details in a form and then submit it, the form gets saved in an access database. I have all that setup and it works perfectly. I uploaded the forms on a sharepoint site and when I open them up from my computer they submit properly to the databse. However when I open up the forms from another computer they don't submit to the database. I know why it does that (because the databases are not on every computer). I can't however figure out a way to solve this problem so ANY help would be amazing. I was thinking to have the access databse on sharepoint as well so the users can download it but won't that just make it that every single computer will have a different saved database? This problem is driving me crazy.
Thanks in advance for all the help.
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
Hello All
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
Regards
Terence
London
Hi everyone!
I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:
Week: Format([Date];"yyyy ww")
The problem is that when I go to datasheet view, it is sorted wrong.
It looks like this:
2007 1
2007 10
2007 11
2007 12
.
.
.
2007 19
2007 2
2007 21
But I want it like this;
2007 1
2007 2
2007 3
2007 4
.
.
.
2007 9
2007 10
2007 11
and so on...
The other column has the rihght values for each week, but I need it in the right order...
What am I doing wrong? Or how can I fix it?
Thanks in advance
Jake
Hello all!, I am new to the form
I have one problem I can't seem to get to the bottom of. I have a database that counts the attendance of my basketball team. Everything works great but I have one problem. Our basketball program is 12 weeks for each student. Now students can join any time but all of them are required to stay 12 weeks to complete the training. How do I get Access to tell me how many weeks a student has left in the program? As i was saying it is easy for me to add together the days of the week that the student attended, but I can't seem to figure out how to get the amount of weeks a student been attending. One more thing, I know this is asking alot but is there a way to have an extra column that allow me to add additional weeks to certain individuals (for the students that like to skip a week or to)?
Any help would be appreciated
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Hello All
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
Regards
Terence
London
I am trying to convert a week which is entered in a text box on a form to the first day of the month in which it falls.
My text box (txtdw) is in the format yyww and I want it to be converted to 01/mm/yyyy.
example: 1401 would convert to 01/01/2014. 1406 would convert to 01/02/2014.
I am pretty close, I can get to the correct month but I can't work out how to change to the first day of the month.
Code:
DateAdd("D", Right(Me.txtDW, 2) * 7, DateSerial("20" & Left(Me.txtDW, 2), 1, 1))
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
=Int(DateDiff("d", Date1, Date2) / 7)
but this does not work for the 5 week months?
Any help would be greatly appreciated.
Hi,
I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.
That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.
I have a contract end date in table.
I have tried
>=DateAdd("m",2,Now())<=DateAdd("m",2,Now())
only to get all sorts?
>=DateAdd("d",60,Now())<=DateAdd("d",68,Now())
only to get all sorts of things appearing?
Anyone that could help would be great.
I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID
Thanks heaps!
C
I've got a database used daily by 4 users. It's split into a frontend (10MB) with all the forms/queries/reports/vba and a backend that's just tables (170MB), and the users access the database from a network drive. All additions through the forms are logged to a text file, and at the end of the day, a report is run that prints the day's work to a PDF. The database / workflow has been stable for the last few years, with only minor edits to the code, and no programmatic changes in the last 3 months.
Today, after running a compact-and-repair, I realized that the database contained no entries added within the last 2 weeks. I checked my log files, and sure enough I see that all of my records were at some point added to the database. This is supported by the fact that I have PDFs for every day in the last 2 weeks that show exactly what was done (roughly 30 new records/day).
My first guess was that compact and repair had corrupted the database, and knocked out a chunk of records. Fortunately, I've got daily backups, so I started restoring to yesterday's database. At this point I found that the records were missing from there, and from every backup from the last 2 weeks. Now, it's possible that my backup solution (logMeIn backup) is hosed, but the the log files are getting properly restored by the backup, which leads me to believe the backup is working. So, somehow these records were never saved in the database, yet they magically appeared in my end-of-day reports?
I thought maybe I was getting stuck in some state where the database went read-only and the edits were getting stored in memory but never written to disk, but that doesn't make sense as we occasionally restart the database during the day for other reasons, and the end-of-day reports are always complete, which knocks that out. Having restored to a prior version of the DB, I attempted to make changes / add new records and they appear to be sticking, but I find my faith in Access rather shaken, all the more so because I haven't a clue what went wrong before.
My database includes two separate dates related to a vehicle purchase and default payments resulting in repossession. I need to find out how many weeks passed between those dates.
Example:
03/14/12 Date of automobile purchase
06/06/13 Date of last payment before default
How many weeks in between? And then I need to average all of those figures so my company can see the usual week-term time frame that our customers are defaulting and getting repossessed.
Hello All,
Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems
1-When I have selected the report and click the preview button. Report is not opened.
2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
Thanx
ami
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex:
Table contains the following details:
Name Branch
Bob 111
Bob 222
Joe 333
Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2
Bob 111 222
Joe 333
Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
Super Thanx.
O.
Hi all
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Cheers
Rhys