I can't get this to work! I am re-using a database to create new reporting. It was a technician utilization database that I am reworking to show weekend days worked. I changed the date format to Long Date to show the day, then started a query. The query is looking for *Saturday* but pulls nothing in the date field that I just changed to Long Date. What am I missing?
View RepliesIs there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
I'm trying to create a sub to count how many days between 2 dates excluding Holiday and weekend. Using the DateDiff() function is not allowing me to exclude certain days.
View 2 Replies View RelatedMy subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)
in a text box, and it works fine.
But I don't know the syntax to get these numbers:
the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.
Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))
The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.
They are: the appointment date and its date parts - defined in numbers.
App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)
how to get my weekend work and not work total day count?
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
Not sure what happens.
occassionally i will run queries that have run before,
the hourglass will turn on, then turn off, and access does nothing
visible, but tack manager says its running 90% cpu. . .
any ideas on what is happening or i am doing wrong?
thanks
sportsguy
How to make pdf file in access 2003.
I want to make pdf of a report in access 2003.
how to have our Access 2003 query total our report column "ProfitLoss`and produce on our report a Total Value of -$420.17
View 13 Replies View RelatedI'm using Access 2003 connecting through ODBC. I'm trying to set up an automated query to grab today's data. normally it's pretty easy, all you'd have to do is enter date() in the criteria in an access query. Unfortunately, the date in my database (i can't change this) is in an odd format.
example: april 1st, 2013 = 1130401
Every single date always has that preceding "1" in front of it, then the year, then the month, and finally the day.
I want to always specify the current date whenever the query is run (without having to type it in)
I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.
So I have Table A:MemberID (autonumber, Primary Key)
NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level
I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:
TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8
So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.
Table B: I want a list of "friendly name" for the different training levels.
TLID (Number, No duplicates)
TLFriendlyText (Text)
So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.
Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].
How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.
MS Office 2003..I have 7 queries that I use to export data to one excel workbook. Inside the workbook are 7 worksheets, one for each query. I have it working but I dont know how to save the exports steps like I did in AC2010.
I export the data by right clicking on each query and select export , then I browse to the workbook and because each query is named differently , it names the worksheet. I have to run these 7 queries each month , so I would like to save the steps and then build a macro that would run all seven steps at the push of a button.
I have saved the steps on a PC running MS office 2010 and the macro works great. The problem is this resides on a PC that cant be upgraded and I need to make it work on Access 2003 .
I have datasets in tables for some price data, which is monthly. Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc) I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month. Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?
View 3 Replies View RelatedI have three tables the tblStudentInfo (holding the persons main data), the tblEducation (holding the students education records) and the refInstution (holding all university's and college information).I am pulling PersonID, name, etc from the tblStudentInfo, education records from tblEducation.
the problem is that in the query it displays the institution ID instead of the Institution name. How do i get the query/rpt to show the name instead of the ID?
On the form Institution this is pulled from the refInstitution table and the ID InstName etc is a combo box 0; 1.0; etc so the display on the form shows the name of the university instead of the ID number.
I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).
The SQL looks like thiss:
SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];
So the data DL-1, DL-2, DL-3 is from the same database.
I have a dlookup which gets the empid on login - i know this works as i fire a msgbox with this, this is stored in a global variable.
i have the following code running on 'form_load' to set securities however its just using the first records and not the ones linked to the specified accont - i have a second messagebox fire just before these dlookups just so i know the global is correct
Code:
Private Sub form_load()
DontKick = 0
DoCmd.SetWarnings False
[Code].....
The EmpIDLogin is 1786 (my own) and i have pasted a copy of the table in question (well a portion)
its only using the first row of checks to provide permissions.
I know how to add a date to a field but is there a way to add another file next to it that adds 2 days, but will make sure the new day is not a weekend day (Saturday or Sunday?
I know it sounds crazy as hell but im trying to avoid Saturday & Sunday for the team's weekday report. Any help would be thankful!
I track my daily work in an access data base. very simple. each day I enter the date, activity, the person I was helping, and a value ie. 1.5 hours etc.
I might work on the same project many times that week, i.e. 1.5 hours on Monday, .5 hours on Tuesday, and 1.0 on thursday.
my new boss wants a weekly summary. so I need to create a query that would show the total for this activity 3.0 hours for that week. So basically at the end of the year, I would have a query with activities by row, weeks across the top i.e. 52 columns, and the total of the various activities summed up for each week.
I looked at all the standard date functions, and did not find a weekend type functions.
Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?
View 3 Replies View RelatedI've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.
These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.
queryDoTheseFirst:
Code:
SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity],
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID])
ON [Priority List].Activity = ImportTemp.[Inspection Activity]
[Code] ....
The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.
I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
View 1 Replies View RelatedI have a tblCommets that has 2 columns in it. The first is an identifier (1,2,3) and the second column is a comment associated with that identifier. Is it possible to use a query to pull that comment based on a user selection? So, if the user selects 1 then the comment associated with 1 is pulled, but if the user selects 2 then that comment results.
Code : =Select[tblComments].Column(2)
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
Table 1:
Account Number
Start Date
End Date
Cost data**
Budget data**
Table 2 (Imported excel file with cost/budget data):
Account Number
Cost data**
Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.