Returning Data For A Set 13 Period Sequence
Jul 2, 2005
Hi guys
Well Im stuck!
I have to produce a report (preview first)that acts like a cross tab query. By this is mean that it reports specific data in a 12 period sequence for each year. Therefore I have to set up dates which are NOT normal calendar dates ie one could be 02/05/05 to 29/05/05 then the next would be 30/06/05 to 24/07/05
These dates are issues at the start of a contract year. I have to therefore produce the report for each period that is given to me ( not an easy one is it) I have considered many options but I cant quite get the final effect
Any Ideas ??
gareth
View Replies
ADVERTISEMENT
Jan 17, 2006
Ive been pondering on this problem for far to long now so decided it was time to ask here !
I would like to return (based on a query) some records that would populate a report. I can set the source of the report as the query but my issue is that I need to actually have some code prior to report population.
EG if they have two products I want to list them product1/ product2 and as far as I know this isnt possible when you just set the source for the report...
The data is coming from SQL server so I have the option of a stored procedure but from trsting that seems to make things even more confusing.
Any help appreciated as Ive been stuck on this for far too long !!
Phil
View 2 Replies
View Related
Jan 13, 2006
I am trying to select all projects with the keyword "sett" in them. About a thousand rows with this keyword exist but why isn't my query returning any data? :confused:
Here's an example of the data "STPT-SETT-NY-EQ Legacy"
Now i want to return all rows with "SETT".
What am i doing wrong here please :)
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName) Like "%SETT%"));
this doesn't work either. No records are returned when query is run:
SELECT projectList.*
FROM projectList
WHERE (((projectList.projectName)="%SETT%"));
View 2 Replies
View Related
Jan 21, 2007
I have 1 table with multi Text columns (location, coaches,ssn etc), and
multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only
bring back the location for each person that had the 'max' sales (pts).
ex. ssn xxxxxx has 3 sales codes : code 1&2 had less sales aquired
then sales code 3. Sales code 1 was location Vt, slsCd 2 was location
CA and slscd 3 was location FL. I need to bring back the sum of all
three codes but only the location FL as it had the most sales pointing
to it.
I have deadline of Monday to get this done.PLEASE help
View 2 Replies
View Related
Sep 7, 2007
O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.
I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./
Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms
I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.:confused:
Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??
Thanks.
View 9 Replies
View Related
Oct 17, 2014
I have 2 tables, one called Location and the other called PC Inventory. They both have columns called sites. The query is set to search both tables by site name and return the PC's at that site that match the search criteria.The search form has a drop down menu with all the site names. When you choose a site name from the last, the after update event does a re-query to a subform on that same form. The source object of the subform is set the query.[Forms]![PC Search]![Combo6] - this is the criteria on the query that point to the drop down menu.
Private Sub Combo6_After Update()
Me.Child19.Requery
End Sub
This is the requery that point to the subform (child19)..The problem is that some selections from the list don't return any results to the subform. But there is clearly records that meet the criteria in the table. Example: ABC @ Maryland returns all the PC's at the site. DEF @ Maryland doesn't return any PC's at the site, but there are records with that site name.Even if go into the table copy the site name that isn't working and enter into the search field it doesn't return the results.
View 3 Replies
View Related
Jun 30, 2015
Any new data I add to my database isn't showing up when I run a query.I've noticed it on 2 queries. One which is my ChargerSearch query with the field name:
Code:
ChargerSearch: "Charger ID: "+[Charger_ID]+" "+"Scheme Contract: "+[Scheme_Contract]+" "+"Nissan Serial Number: "+[Post_Serial_Number]+" "+"Client: "+[Client]+" "+"Site: "+[Site]
and the Criteria:
Code:
Like "*" & [Enter Search Term] & "*"
And my JobHistory Query which has multiple fields from many tables. With criteria only on the Charger_ID field "[Enter Charger ID]". Both work fine for data that was already there but any new data I've added isn't returning any results when I enter the correct values in the parameters. The data is definitely there in the appropriate tables, it just won't find it on the queries.
View 11 Replies
View Related
Aug 20, 2013
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
[URL] .....
View 3 Replies
View Related
Oct 11, 2014
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
View 4 Replies
View Related
Dec 29, 2006
Greetings,
I've been banging my head on this for days, and what really makes this difficult is the number of websites which say it should work just fine!
I'm working with Access 2003 and trying to make use of the IIf function. I have given my fields generic names in the examples below.
I have a number of items I want to search upon in my search form. Each item has a check box and a combo box. I want the query to look at the check box. If the box is checked, I want the query to use the value in the combo box. If the check box is UN checked, I want the query to ignore this field and return all values. I thought this was simple, until I tried to do it!
Here is my code, placed in the Field-name criteria box of the query:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name])
Now I know that the function works, because I can manually add a value:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],2)
This will return all records where Field-name = 2.
So... my original code should work! Because no "false" condition is stated, the query should be blank, and should return all records. But it doesn't! Truly maddening.
Here are some attempts which did not work:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],>0)
---> The ">0" caused an error. In fact, I can only specify an integer - all else, including things with operators, do not work.
Working with nulls - it does not seem to recognize an unchecked box as a null.
That's the short version of the story - any and all ideas welcome!
Regards,
-- Mark
View 14 Replies
View Related
Mar 5, 2007
The 1st of each month I have to do a report that showes the number of cases closed the previous month and I have the following criteria in my query in my date entered field: >=DateSerial(Year(Date()),Month(Date())-1,1) And <DateSerial(Year(Date()),Month(Date()),1). This gives me all cases closed the previous month (for March it would show me all cases closed for Feburary).
I also have to track all the cases from 1-1-06 to the month prior to the previous month mentioned above, January in this case. For March my report needs to show all cases closed from 1-1-06 to 1-31-07. I would like to automate this so that my query knows to look for all cases from 1-1-06 upto and including the the month prior to the previous month of my report.
View 2 Replies
View Related
Dec 1, 2007
i have query tthat calculate perioe between date. the result is number.
how can i modify so it will show something like 1 year, 2 moths 3 days?
Thanks
View 9 Replies
View Related
Jun 16, 2015
Query that would flag me if a sum over any 7 day period reached a certain amount.
Setur : I have a query of SAMPLEs. Each sample can have a PERSON associated with it. The samples have an AMOUNT also associated with them.
What I'm trying to do is set a query or flag of some sort if during any seven consecutive days a PERSON has samples associated with them where the summed amount is some value, we'll say 10. I have no problem summing an amount associated with a person over a 7 day period but am having trouble trying to visualize how to do this for any seven day period without having a sum for every 7 day period throughout history.
View 4 Replies
View Related
Jun 9, 2007
I am working on a database for work. It is a processing plant and once a product is complete it is entered into a table. At the end of each shift the workers will be logging out. What I need to figure out is how to just get a total product processed a 12 hr period. The periods will always be either 6:30 am to 6:30 pm or 6:30 pm to 6:30 am. After it is complete it will be added onto a report.
View 4 Replies
View Related
May 16, 2005
Hi,
I am looking for guidance on how to get a query field to display a numeric period from an existing field that contains a date.
ie:
01/01/2005 = 1 (1st Month)
01/05/2005 = 5 (5th Month)
Etc.
Thanks
Darrell...
View 2 Replies
View Related
Jan 28, 2005
sooo the people i work with are morons and will leave their desks with the db open and forms open to records. i know unbounded forms would solve this problem, but i also don't like the db being left open for anyone to access if they leave their desks. i want the DB to count down from 10 mins after the last action and then display a message that says
"you have been inactive for 10 mins. click 'Yes' to have the program remain open. click 'No' to exit the program. if you don't click either, you will be automatically logged off in two minutes."
then once this message pops up (maybe a modal form? or maybe a msgbox), a two minute timer begins, and if no button is clicked, the db closes itself.
i know this is possible, i just haven't used timers yet and am unfamiliar with how to use them. thanks
View 1 Replies
View Related
Jun 10, 2012
How do I enter and store a period of time that may be of any length: years (.. unlikely but ...), days, hours, minutes, seconds (or even micro seconds) that I can later add to other dates or times stored in my database? How do I create a sensible data entry mask for yyyy/ddd/hh:nn:ss.999 and then convert this to a field compatible with date/time functions? And what format should this data take (be converted to ..?) and be stored in?
View 2 Replies
View Related
Aug 23, 2005
Hi,
I've got a combo box that uses
DatePart("ww",[Date],2) AS WkNum
to obtain the week number.
How would I obtain the start date and the end date from just the week number?
Is it also possible to change WkNum to the week number of the selected month?
E.g. if December is selected, instead of week 53, show week 5.
Any help would be much appreciated.
View 2 Replies
View Related
Feb 23, 2008
Hello all,
I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08
Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08
Previous entry Date = 20/08/07
Textbox on Form = 1
View 4 Replies
View Related
Feb 8, 2015
I create form to calculate the difference between clock-in time and clock-out time, what is the code to do that
I have 2 fields :
field 1 name clockin
field 2 name clockout
I have a text box to display result equal the period between time1 and time 2...
View 7 Replies
View Related
Jun 4, 2013
I have a table that contains a person ID, a start date for that person, and a GroupType. I want a way to count the number of people who started within ANY 40 day period. Then depending on that number , assign a value to the GroupType field (ie. if the no. of people is 1-15 then groupType = groupA, if its 16-30 then groupType = groupB, if its over 31+ then grouptype = GroupC. This needs to be re-counted every time a new record is added.
As well as having the groupType visible on the form for that person, I also want to be able to run a report showing all people, their start date and group type. Note that when start dates are entered these may be either future dates as well as dates in the past.
If possible, I would also like to track /store when a person's group type changed in another or the same table perhaps.
View 4 Replies
View Related
May 25, 2006
I have duplicate loan numbers in a table. I want to create a query that will assign a number so the the duplicates will have a new field called sequence.
example
LNNUM SEQ
999999 5
999999 4
999999 3
999999 2
999999 1
888888 3
888888 2
888888 1
777777 2
777777 1
Any suggestions?
View 2 Replies
View Related
Aug 2, 2006
Can you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.
View 1 Replies
View Related
Aug 3, 2005
Hi,
Over the past three months I have been trying to develop an application which can repeat appointment entries.
I would like to have the option to repeat an appointment entry for a given number of weeks.
For example if an appointment entry was placed for today (Wednesday), there would be a command to create additional duplicate enties for the same day of the week for a given period of weeks ie say every Wednesday for the next 52 weeks.
Any help on how I could approach this would be very much appreciated.
Thanks
View 1 Replies
View Related
Sep 8, 2007
I would like to make a query which is based on a table that holds information on employees (past and current)
I take the fields:
Name
Entry Date
Exit Date
and now would like to write a query where I can select a start and end date and the outcome should then be the "active" employees during this time.
for example between 1-7-2007 and 31-7-2007
I can not get my head around the criteria as the dates lie in 2 fields.
The exit date field can be empty or have dates that lie outside the range (see above) like the end of a contract at 30-10-2007
Hope someone can give me a tip to get me on my way.
View 5 Replies
View Related
Jan 30, 2008
Hi there
I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.
I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?
View 1 Replies
View Related
