Query To Track Files Last Status
Sep 27, 2006
Hi folks,
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
Files_id int
Filetransdate date
Filestatus_id int
A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
Regards
Charles
View Replies
ADVERTISEMENT
Jan 9, 2014
I have a query called "Program Status Count" that keeps track of the current status of various programs. There are three statuses: Closed, Deficient, and WIP.On the Query, I used "Total:" row and have two fields: StatusID field set to "Group By" then in the next column I have the same StatusID Field set to "Count" (it auto-renamed itself to CountofStatusID)...which on the query does what it is supposed to do:
Closed, 2
Deficient, 1
WIP, 1
When I go to the Report Design and try to make a Pie Chart (or any graph) it asks me what query I want to use, so I point it to the Program Status Count Query and select the only two fields I have in the query.This is the Row Source Code:
SELECT [StatusID],Count([StatusID]) AS [CountOfStatusID] FROM [Program Status Count] GROUP BY [StatusID];
Sometimes, it will show the appropriate fields but then it will disappear in Report View, switching to Print Preview does not fix the issue. Actually, it often deletes the correct information in the chart and use the PK autonumber instead of the words.Othertimes, the default data shows up and nothing I do changes it. I have even done the copy/paste of the query table in the report (doesn't work when the graph needs to update.
View 3 Replies
View Related
May 25, 2005
i am trying to make a cross tab query that will give me the status of the unavalble rooms in my hotel
somthing like that:
romm num today today+1 today+1 today+2 today+3 today+4 and so on...
1 X X
2 X X X
3 X X X
4 X X
5 X
thas how i will see where do i have a free rooms in a range of dates that i want to insert.
please advice,
Thanks
View 1 Replies
View Related
Sep 7, 2005
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus
FROM SiteStatus
WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
View 2 Replies
View Related
Feb 4, 2007
Hi there,
i am writing a query that calculates a date
ExpiryDate: DateAdd("yyyy",[NATATable].[NATAExpiryPeriod],[NATATable].[NATADate])
which works
The expiry period is always 2 or 4 and the natadate is a date i want it to add 2 or 4 years to the date the NATA was done... works no problems.
Now i want a query in another field as a status
ExpiryStatus: Expired or Valid
ExpiryStatus: IIf([ExpiryDate]>=Now(),"Expired", "Valid")
Which works
i am ultimatley aiming to end up with 3 Fields - Expiry date (working), Status (working) and Remark (not working)
Remark is based on this excel query
=IF(AL3<NOW(),(IF(AL3<(NOW()-365/4),"(3) Expired more than 3 months ago","(2) EXPIRED during last 3 months")),(IF(AL3<(NOW()+365/4),"(1) Expires within next 3 months","(4) Expires in more than 3 months time")))
which i need to change to an acess query.
Where AL3 is the expiry date.
Please help
Thanks
Kris
View 1 Replies
View Related
Jan 25, 2007
I'm trying to create a report eventually, however, I need to get my query set up.
My report should look like the following when complete.
Timeframe (mth,qtr,year)
# of Total Projects Completed - regardless of status
# and % of Projects Completed within Requested Delivery Date
# and % of Projects Completed within Committed Delivery Date
# and % of Projects Completed within Requested and
Committed Delivery Date
I have 3 different fields to base my calculation from:
Actual Delivery Date
Requested Delivery Date
Committed Delivery Date
I have created 2 new fields that calculate the number of days:
[Actual Delivery Date] - [Requested Delivery Date]
[Actual Delivery Date] - [Committed Delivery Date]
I'm trying to set up a "Status" field to indicate whether the project was completed "Within Requested", "Within Committed", or "Within Requested and Committed", however, I'm stumped. I've tried an Iff statement to no resolve. I don't want 10 queries just to get me there either.
Please help! :confused:
Thanks!
View 2 Replies
View Related
Jun 8, 2015
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".
View 8 Replies
View Related
Aug 14, 2006
Hy, I have this db where a user can purchase and item and total stock number needs to be updated.
I can calculate how many items are left but how to update the warehause table?
I can live without table updating, but if the same item is purchased multiple times my query return incorrect values as the number from which the formula subtracts is never changed.
See attached database.
View 1 Replies
View Related
Dec 28, 2007
I would like to track the revision history of table records. To do so, I have created tables with two-key fields. A typical table is tblPO_Data. The first key field is PO_Number and the second key field is PO_Revision. Data fields follow.
A key element of this table is that each record may have any number of data fields left blank AND later revisions may only show the "changed" field values.
To Illustrate tblPO_Data:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
100 _________0___________10_____15___________________2 ____20
100__________1_________________________30
100__________2_________________________35____100
200 _________0_______________________________________1 ____10
NOW I would like to create a query that lists one record for each PO number, showing the most recently revised data. The desired result would look like:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
100_____________2_______10_____15_____35_____100__ ___2____20
200_____________0_________________________________ ___1____10
Please realize that this is an example. There would be numerous one to one tables for each PO number to reduce the number of blank fields (ie. only tables with changes would be get revised records).
Can someone suggest a way to create a query to accomplish the above requirements, thanks in advance, Jeff...
View 3 Replies
View Related
Feb 12, 2007
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
View 6 Replies
View Related
Jun 19, 2015
I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"
The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.
How can I run a query that will show me the names and status instead of the numbers?
I have attached the database (2003 format) ....
View 2 Replies
View Related
Jul 25, 2007
Hi
About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.
However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.
I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?
I have asked the users and (of course) they don't know.
TIA
View 3 Replies
View Related
Nov 20, 2012
I have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.
I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.
I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.
View 14 Replies
View Related
Feb 10, 2014
I need to design a query that contains about a dozen fields for stationery items along with another dozen fields (checkboxes) to show whether these items were delivered (Yes, I know, not great db design).
I ran out of space with the the query criteria window.
View 6 Replies
View Related
Nov 11, 2013
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
View 7 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
May 2, 2012
Every hour a zip file is created in an FTP folder. The zip file contains an .mdb access database file with any changes to the data since the last hours file.
What is the best method to get access to connect to the content of the zip files and pick up the changes to the data?
View 3 Replies
View Related
Jan 24, 2014
I have a very large query that I need to export to text. I'm hitting the 4GB limit, so I'd like to "split" the query and create two text files instead.
What's the most efficient way to do this? Also, are there any export file formats that can handle more data? (i.e. is there a format that could fit this massive query into a single file?)
View 10 Replies
View Related
May 4, 2006
I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...
View 1 Replies
View Related
Oct 16, 2006
Would anybody know how to insert a status bar on a form and control it.Need help on this.Thanks in adv.
View 1 Replies
View Related
Aug 30, 2007
I've been avoiding this project but it has come up again. I have a file given to me each month. It contains a member number and status. The status could be any of the reason codes the user comes up with. So far there are ten. I imported the file into an access table. The users need a report summarizing the status changes if any. If there are any changes; the reason code will have a X under the reason code which pertains to them. I ran the unmatched query; but that gives me if the person was added or deleted. How do I search the reason code fields to check if there have been changes. If I ask for the nulls or is not null for each field; How would I know if it was null or not null in the beginning. I need a way to check to see if for example: Smith did not have a X under reason code 1 for the month of June but does in the month of July. I hope I didn't confuse anyone. Please help
Thanks
View 4 Replies
View Related
Mar 17, 2005
Does anyone know how turn the status bar off with code. I need to turn it off when the main form opens. Then when it closes I need it to come back on...
Thanks,
Ken
View 5 Replies
View Related
Jan 17, 2007
Hi,
My situation:
A front end written in Access (back end mySQL).
I have made a .mde file out of this front end.
There are a number of MS Word documents used (as merge documents) for running reports and letters that are linked to queries in the database. However, I can only link these documents to the original .MDB file as you can't link to the queries ni the .mde file.
This works fine until you have a query that requires a parameter from a form. The user will have selected the parameter from the .mde version of the front end that they are using but the query which is being called from the .mdb version (by the Word merge document) can't see this parameter because it is a totally separate database.
How can I get around this problem?
Sorry this is a lengthy post, hope it makes sense.
Matt.
View 4 Replies
View Related
May 21, 2015
I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).
Example:
PLAN ID
Number
Amount
Status
AM141
12345
100
Disconnected
AM141
54321
5000
Active
AM142
11122
2000
Disconnected
AM155
22334
500
Disconnected
I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:
MACs Report AM141 20150521.xls (both records above should be in this report)
MACs Report AM142 20150521.xls
MACs Report AM155 20150521.xls
View 3 Replies
View Related
May 30, 2006
Hallo Everyone,
I am working with the database that consists of more than 15 tables with more than 50000 records and the problem is that more than 10 people use the database. So in this case I am not sure whether who has the changed the data in it.
So for this reason i need to create a logfile to determine what are all the changes made in the data in the database with the user who uses that.
(I need to create a history and save it.)
is that possible in MS ACCESS? if so can anyone tell me some suggestion
aravind.s
View 3 Replies
View Related
Apr 14, 2005
Hi! I am new to Access and am trying to create a database for my customers that tells me what product/service they avail of and the status of their payments.
My tables are
Customer Info
CustomerID
LastName
FirstName
Address
ContactNo
Products (A,B,C) ( A customer can only purchase one product)
CustomerID
ProductID
PaymentSchedule (Annual, Quarterly, Monthly)
CustomerID
PaymentSchedule
Annual Product Fees
Product
Fee
Quarterly Product Fees
Product
TotalFee
QuarterlyFee
Monthly Product Fees
Product
TotalFee
Downpayment
MonthlyFee
Am I doing ok so far? I wasn't sure How to make my tables since the amount payable is dependent on both the product type and the payment schedule. Any suggestions?
View 3 Replies
View Related
