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

Reports :: Keeps Track Of Current Status Of Various Programs

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

Status Query

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

Trying To Create A Status Query

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

Query - Status Upon Date

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

Query To Set Status Based Upon 2 Fields

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

Queries :: Query Multiple Status

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

Keep Track Of Stock Items By Query

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

Query To Track Revision History

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

Record Lock Status & Exist Query

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

Queries :: Matching Users And Status In Query

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

Track Form/Query/Report Usage

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

General :: Track Number Of Records In A Query

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

Queries :: Criteria For Multiple Checkbox Status In A Query

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

Modules & VBA :: Loop Through Files And Then Compare With Files In Database Table

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

Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

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

Using MDB Files Which Are Backed Up Hourly Into Zip Files

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

Split A Query Into Two Text Files?

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

Form That Searches Query With 30+ Linked Xls Files

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

Status Bar

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

Status Changes

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

Status Bar

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

Calling A Query Using .mde Files And Merged Word Documents.

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

Access Query - Export To Multiple Excel Files

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

Track Changes

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

Am I On The Right Track?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved