I have a tabbed that contains a form NavigationSubfo rm (this was auto-named so I don't know if it goes in the syntax for what I'm trying to do)
I am concerned with a form named Suspend_Trendin g_Dashboard. A chart on this form is named chart. When the form loads, I don't want the query that is the rowsource of the chart to run in order to speed up the database. I have two text boxes for begin and end dates. These boxes are the criteria...
robin a
Last Activity: Apr 10 '13, 06:34 PM
Joined: Feb 13 '12
Location: henderson, nv
-
Chart in tabbed form goes blank on records source change
Last edited by zmbd; Apr 10 '13, 10:10 PM. Reason: [z{}{removed extra space and return in code block}] -
zmbd,
You did excellent. I do have a table that lists the report names and frequency. I also have a table with report name and date received. I don't want to store a date for every single day in a table to be able to compare date rec'd to date due for daily reports. My ultimate goal is to have a query that calculates the number of reports delivered on time as a percentage to the number of reports delivered period. then I also want to calculate... -
count how items not received by date
Hi,
I need to capture the percentage of sales reports that are not received on time. I have daily, weekly, monthly, annual, and bi-annual reports that i need to track. e.g. three differenct reports are due each day. If a report was not received each day during the the month-to-date or year-to-date I need to get a count so I can report the percentage of reports that weren't delivered on time. I've been mulling around the coundif statement... -
sorry, i since removed the ReqUID field from the query and it works fine, but I don't understand why.Leave a comment:
-
thank you, i did get to that point after i asked for help. You are quick, (Rabbit). I built the queries but for some reason my update query makes the data in the field to be updated blank. This is my update query:
...Code:UPDATE Temp_Join_tbl INNER JOIN Join_tbl ON Temp_Join_tbl.ReqUNID = Join_tbl.ReqUNID SET Join_tbl.ReqUNID = [Temp_Join_tbl].[ReqUNID], Join_tbl.StatusLast edited by zmbd; Dec 14 '12, 06:03 PM. Reason: [Z{Stepped SQL for easier read}{added set of code tags}]Leave a comment:
-
Append query with criteria
Hi,
I have an append query that has to add a record to a table [Join_tbl] if the field [ReqUNID] does not exist (there are other fields added as well with their own criteria) OR If the [ReqUID] already exists but another field [TaskStatus] has changed, then overwrite the [TaskStatus] field only. This is the code I have that only adds a record if the [ReqUID] doesn't exist.
...Code:INSERT INTO Join_tbl ( ReqUNID, TaskTeam,Last edited by zmbd; Dec 14 '12, 06:08 PM. Reason: [Z{Stepped SQL for easier read}{missed a step:)}] -
You are e genius! it works perfet. I can't thank you enough!Leave a comment:
-
what do you suggest? i didn't create the database but I can change things.Leave a comment:
-
thank you. another direction that popped in my head is to count increments of one between the two period dates but then how would it count between 11.09 and 2.1, which should be 2? thanks again.Leave a comment:
-
Yes I would. thank youLeave a comment:
-
Extract date periods for calculation
I have a query in which I need to get the number of periods between and including the start date and end date for a project to use in a calculation. There are 13 periods in a year. I can't use exact dates for the periods because they change each year. The period is a field in a table and is stored like this:
Starte Date End Date
1.1 3.1
2.1 5.11
3.09 2.1
1.1 is period 1 in 2010, 3.09 is period 3 in 2009, 5.11 is... -
Thank you everyone. I now know NOT to use the multivalue data type. Turns out my existing design included a join table and it works well. I just don't like having to put in a class up to 40 times BUT, I do have a selection for ALL that assigns to every employee. Everyone's advice was very clear and helpful. Thank you everyone.Leave a comment:
-
Seth,
Are you saying that after creating the join table, I would still add a lot of records to the class_catalog table? e.g. with a multivalued field it would look like this:
Class_ID Job_Title Class_Name
1 machine operator, handpacker, coach first aid
if it isn't multivalued it would look like this:
Class_ID Job_Title ...Leave a comment:
-
I wanted to add that there are only 38 job titles. would making job titles a mutli-valued field work or would it complicate all of my queries?Leave a comment:
-
storing data in a table
I am using access 2007. This is a training database in which classes are assigned to employees by their job title. I have the Class_Catalog table which stores all class information, including a job_title field. My problem is, now that I am importing data, I've found that since up to everyone in the company (450 people) are required to take many of these classes. I don't want to add the same class 450 times with each applicable job title because... -
if then function with 1 condition but 2 actions
Hi,
I have a form I use to update employee records. In the "on click" event of the save command button, I need to run 2 queries if a condition is met, else simply save the record. I don't know how to set this up. I'm trying to do this:
If([condition1, then run query1 and run query 2, else goto new record.
thank you -
Hi again,
I have two queries now. If I choose dates on my form from the present year, then the results are fine, but, if i choose 2011 or something else, it all goes out of whack. I'm at a total loss. here is the sql for my first query:
...Code:SELECT DISTINCTROW (Format([RECORD_DATE],"mm/yyyy")) AS Record_Month, FtagData_tbl.LINE, FtagData_tbl.TYPE, Sum(IIf([FtagData_tbl]![DATE_COMPLETED] Is Null,1,0)) AS [Open], Sum(IIf([FtagData_tbl]![DATE_COMPLETED]
Leave a comment:
-
oh, and i am using access 2007. I did change the ftagdata_tbl name but i kept RECORD DATE as is for now. My query returns no results, so i didn't get the same as yours. my PK for the ftagdata_tbl is TAG_ID. I don't know why yours works great and mine doesn't.Leave a comment:
-
Thank you so much for your help. Here is the SQL from the query:
...Code:SELECT Format([RECORD DATE],"mmm/yyyy") AS MonthandYear, Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Null,1,0)) AS OpenRecords, Sum(IIf([FtagData_tbl]![DATE COMPLETED] Is Not Null,1,0)) AS ClosedRecords FROM FtagData_tbl GROUP BY (Format([RECORDLast edited by zmbd; Aug 21 '12, 04:33 PM. Reason: R:(I changed my query to the one you suggested) - Z:(reformated the SQL for easier read)Leave a comment:
-
query won't group by month
Hi,
I have a query based on a table [Ftag Data tbl] that returns [RECORD DATE], a total of all records where [DATE COMPLETED] IS Null, and a total of all records where [DATE COMPLETED] is not null. The dates have the criteria of Between [Forms]![Chart_selection]![TxtBDate] And [Forms]![Chart_selection]![TxtEDate]
Which is a form i use to select criteria. My problem is that I need the totals to be grouped by month instead of showing...
No activity results to display
Leave a comment: