Top/Max/Subqueries ... Losing My Mind
Mar 8, 2008
I've looked at so many options for writing this query that I can't see the forest for the trees and hoping you kind folks will point me in the right direction.
I have a table called services that includes:
service_id
prop_id
service_year (ie: 2007, 2008)
service_date (ie: 3/3/08)
service_id is a unique ID for each service which relates to data I will need to pull from another table.
prop_id relates to a property ID in another table, not unique as multiple services against one property.
The problem that I have is that each prop_id can have more than one service_date for the same service_year. I need to be able to find the latest service_date on a prop_id and its associated service_year and service_id.
Any pointers would be greatly appreciated. BTW, using this in Access 2003 & 2007
View Replies
ADVERTISEMENT
Jan 16, 2007
I have a simple, simple query based on a linked SQL table with a date/time field called "CHANGEDT." All I want to do - as I have done many times before - is to set criteria for that field of Date()-10 (records changed 10 days ago). Every time I get an empty data set, even though I know there are records in the table for the target date. There are no other criteria in this query, no linked tables or anything like that - just a siiiiiiiiimple query! Sheesh!
What would make a query NOT respond as expected when calculating a date 10 days in the past? I'm thinking that my frustration is causing me to overlook something obvious.
Thanks-
SLH
View 3 Replies
View Related
Nov 30, 2005
Ok... I have notice that every body and their dog has asked this question and reading all the other posts has helped me along. However i have hit another roadblock. I know that IT IS possible to have a combo box that, when an item is selected, selected fields are automatically filled. Is it possible though to have those auto filled fields retained when a new record is selected in the form.??
Eg.
I have a table with model#s of a product aswell as unique properties of each model...I set up a form with a combo box to select the model# and auto fill the properties of that model into the fields below. when i change the record (record selectors <- ->) the combo box and related fields stay the same. can i make it so that each record is saved idividually and still use the autofill by combo box ....
man i dont even know if this is gonna make sence to anyone...but if it does any help would be extremly appreciated.
thank you
View 2 Replies
View Related
Mar 11, 2008
Hello all,
Using Access 2003, I am creating a db that tracks computers along with what switch and port they are connected to.
I have 5 different networks along with 31 switches.
I have a cascading combobox, in my form, that gives me a list of what switches correspond with the network chosen.
What I would like it to do is give me a list of ports that are open/available based on the switch (available ports ranging from 24 to 48 based on the switch).
Creating 31 tables is a little excessive and I really don't want to have to do that.
Tables I have so far:
tblComputerMaster -
tblNetwork
tblSwitch
along with a few others that don't pertain to the question.
I can dummy down my db if someone needs an example. If anybody has any ideas on how this would be possible I would really appreciate it.
View 14 Replies
View Related
May 12, 2005
I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing.
In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error:
The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement.
The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)
View 1 Replies
View Related
Jul 15, 2005
How many subqueries can I put into one SQL statement? I'm trying to run a query that has two subqueries, but it's not working. It works when I create and reference one of the queries separately, but when I put them directly into the statement as subqueries, it gives me a syntax error. It would really save loads of time to have this in one statement. Can someone tell me what I'm doing wrong with this query or tell me if this is possible to use the AS identifier more than once in a query?
qryTwo: WORKS!
SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID);
qryThree with separate reference to qryTwo: WORKS!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID
qryThree without separate reference to qryTwo: DOESN'T WORK!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID)]. AS qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID
Hopefully I am wording this correctly??? If anyone can help me figure this out, I would greatly appreciate it. Thanks!!
View 6 Replies
View Related
Mar 24, 2006
Hi, I hope someone can help me out...
I've made a bunch of queries to get the data I need out of my table, they build on each other and I find that my date query is used twice. In date query I prompt for the date, when I run the percent query I get prompted twice.
How can I set this up to only prompt the user once for the date?
Here is roughly how my queries are dependent on each other
'percent query'
/
'Average query' 'date query'
/
'subtract query' 'record count query'
/
'sum query' 'date query'
View 1 Replies
View Related
Oct 5, 2006
Hello,
I am having a problem getting the expected results from a SQL subquery. The point of the query is to return all users and user titles that have not been entered into the database for the current month (not in tblHours). Some users have 2 titles and some just one title. Each month we enter hours worked per title so we can track labor progress. The problem lies w/ the individuals w/ 2 titles. The SQL only keys on the user id, but I need to return both user id and title. The variables called iMonth and iYear are passed from the form and the query is executed via a command button. qryTitlesPerUser contains each users title, name, and user id.
Here is the query:
strSQL = "SELECT qryTitlesPerUser.Name, qryTitlesPerUser.title FROM qryTitlesPerUser "
strSQL = strSQL & "WHERE qryTitlesPerUser.UserID AND qryTitlesPerUser.title NOT IN "
strSQL = strSQL & "(SELECT tblHours.UserID, tblHours.title FROM tblHours "
strSQL = strSQL & "WHERE tblHours.month = " & iMonth & " AND tblHours.year = " & iYear & ") "
strSQL = strSQL & "ORDER BY qryTitlesPerUser.Name"
Any help would be greatly appreciated.
Ken
View 7 Replies
View Related
Dec 19, 2006
Short version of question:
My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?
Long version of question:
I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:
Show where we built it last time, and
Allow the user to select where it will be built this time.
The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.
But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.
Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.
Thanks in advance!!
View 3 Replies
View Related
Apr 17, 2014
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code:
SELECT [tblBalances].[BalanceDate] AS AsOfDate,
(SELECT COUNT([tblIssues].[IssueID])
FROM [tblIssues]
WHERE [tblIssues].[Flag] = True
AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code:
(SELECT TOP 1 [tblComments].[UpdatedWhen]
FROM [tblComments]
WHERE [tblComments].[IssueID] = [tblIssues].[IssueID]
AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate]
ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
View 2 Replies
View Related
May 3, 2006
I am building a database for a client that tracks construction drawings. The client came today to check on progress. Just before he came, I changed the start-up properties to hide the database window, menus etc so it would look more like the finished product.
Problem was, I lost alot of the functionality and had to change it back to demonstrate:( . Is this common and can anyone explain why? much appreciated
View 2 Replies
View Related
Aug 18, 2006
Hi Everyone
I wonder if anyone can help me please.
I have a form that nearly fills an a4 sheet of paper when printed. For this reason I have to set the margins from 24.99 down to 5.
This is ok and the form works well and prints out properly about 75% of the time. Occasionaly when I open the form it has reverted back to the 24.99 margins which of course prints out on three sheets of paper and is all over the place.
Is ther a way to set the margins on load and can anyone suggest an appropriate bit of code.
Kindest regards
Tony
View 4 Replies
View Related
Aug 22, 2007
Hi,
There's not much space in the title but I hope I caught someones attention that can help.
For starters it's ACCESS97
The database is split into front end and linked tables.
We added few fields a couple of days ago. ( We have a full back-up)
What is happening now is that after a period of time the buttons stop working and give a message saying something like...
The On Click can't find the file or info that it is supposed to be doing. That isn't the exact words, but I don't currently have the damaged version it is deleted. There is no error message number.
The same thing has happened 3 times. Each time I delete the front end and replace it with the backed up copy. This then works again.
Also....
Once the corruption or whatever it is happens, you cannot add any buttons in design view and also you cannot get to the Event Proceedures through the properties and also the Build Event doesn't let you in either. All you get whet trying any of these is a File Not Found message.
Any ideas out there or do I just have to revert back to the copy before the changes ?
It's a very big database about 90Meg Front end and 8Meg Tables
Help Please.
Thank you
Mike:confused:
View 5 Replies
View Related
Jul 20, 2007
Is it possible to have a query retrieve all information from one table, but only partial information from another?
For example, suppose I have two tables, Jobs and Employees.
The Jobs table is a list of all available jobs in the company; some filled, some not. Each job is assigned a Job Number which is unique.
The Employee table is a list of all employees in that company. Each employee is assigned to a particular job, referenced by the Job Number.
The query's job is to provide a list of all Jobs, but with employee information added to those jobs that are filled.
The Jobs table has two fields; JobNum and JobDesc.
The Employee table has three fields; EmpNum, EmpName, JobNum.
Jobs has five records;
100, Boss
105, Aide
200, Manager
201, Worker
202, Worker
Employee has four records;
111, Montgomery Burns, 100
112, Waylon Smithers, 105
121, Homer Simpson, 201
122, Lenny Leonard, 202
Jobnum is the link between the two tables in the query and its fields are;
JobNum, JobDesc, EmpNum, EmpName
What I want is;
100, Boss, 111, Montgomery Burns
105, Aide, 112, Waylon Smithers
200, Manager
201, Worker, 121, Homer Simpson
202, Worker, 122, Lenny Leonard
But, what I get is;
100, Boss, 111, Montgomery Burns
105, Aide, 112, Waylon Smithers
201, Worker, 121, Homer Simpson
202, Worker, 122, Lenny Leonard
Job Number 200, the Manager slot, is being dropped out.
This is the SQL code for the query;
SELECT Jobs.JobNum, Jobs.JobDesc, Employee.EmpNum, Employee.EmpName
FROM Jobs INNER JOIN Employee ON Jobs.JobNum = Employee.JobNum;
How can I get the complete Job list with employee data added to those jobs which are filled, while leaving the vacant positions - well, vacant, but still included in the query's result?
Thanx!
View 4 Replies
View Related
May 12, 2006
I have a form and subform. Clicking a record in the subform displays the details of that record in the main form. The form is used for editing, adding and deleting data for a given person's sales record and the subform displays a full list of all records for that person.
When I edit a person's record, using the main form, and click on a button to run the code:
DoCmd.RunCommand acCmdSaveRecord
I can go into the underlying table and see the changes. I can also refresh the subform and see the changes.
However, the next time I click on any of the records in the subform, the value in one of the fields vanishes from both the subform and the table. Any other changes made, to however many fields, remain.
Surely, if there's a problem with that field, the update shouldn't happen at all?
View 3 Replies
View Related
Mar 19, 2012
My company had an Access db created back in 2002 in order to create file names (Legal Labeler). The db consists of many reference tables (Figure 1). The person who created the db created a form to use in order to create the file name and labels. As you can see there are various drop downs where the end user can choose which information to include. I do not think that the tables are linked. Once the end user fills in the form they click Save Label Data and the data is written to a master table (Table tblLevelOneSubject).
One of the problems we are having is that records in any one of the extraneous tables (Company Name, SubCatetory, SubCategories L1/L2, etc.) just disappear. For example, in Figure 3, we have all the entries for the drop down labeled SubCategory (A codes are depicted) but, for some reason, we are randomly missing some of the codes (typically all the A codes and B codes); however, nothing from the master table is missing (that is why I do not believe they are linked).
Another major issue we are running into is in the table named tblLevelFiveSubCatTwo (SubCategory L2 on the form) (Figure 4), which is an auto-numbered table, we are also losing records (as you can see it table starts at 7). When I created a file last week I needed to create a new entry in the drop down SubCategory L2 and it was auto-numbered 8 when it should have been 734. Also, the table shows that there are only 697 records but if we are auto-numbering there should be 734 records. Therefore, we are losing records in this table as well.
Is there any way we can secure the tables so this does not happen in the future? When I export the data into Excel it looks as though all of the data is accounted for - it is just missing in the reference tables.
View 4 Replies
View Related
May 26, 2006
Hi,
I am creating a csv in another application where fields may contain trailing spaces. If I open this in excel, the trailing spaces are preserved, but if I import into Access I lose the trailing spaces. Is there any way to preserve them?
Thanks,
Dave.
View 3 Replies
View Related
Feb 10, 2005
Hello,
I have a subform that is based on a SQL statement that sums by Dollar Amount and groups by Account. I have it set up so the user can double-click on an account, which opens an unbound form. I then have the form execute a SQL INSERT INTO statement, which works beautifully.
The form opens as a pop-up, and I have it requery the subform when it closes. However, when the pop-up closes, I go back to the first record on the sub-form. I need it to "remember" the record I was on and take me back to that one. What is the standard procedure for doing this?
Thanks in advance!
Eric
View 2 Replies
View Related
Nov 16, 2005
right I have a subform which holds assessment data
it is a tab-control form which monthly assessment data each tab has its own form i.e. "frmCurrentAssessment-October2005", "frmCurrentAssessment-November2005" and they are based on their own queries i.e. "qryCurrentAssessment-November2005". It will not let me update the subform which is joined to frmAllStudents which is based on "qryallStudent"
the form and the subforms are joined by Admission Number. I can update directly from "qryCurrentAssessment-November2005". But the form wont let me update, I have already checked that the fields arent locked or that the form isnt uneditable and everything seems fine.
I am at a complete loss as to what to do here, is there something obvious that I havent checked
View 1 Replies
View Related
Mar 3, 2015
I've developing a little Project and have some issues after splitting a db.
I have some forms in wich I want to see some tables with the "+" sign on the rows so I can go to their linked tables. The problem that I have is that everything works perfect until I split the access file.
View 5 Replies
View Related
Jul 14, 2005
Still not sure how to do this, after I convert my database and create an mde file few changes might accrue in the future, I have tried to make some changes for testing purpose and then create a new mde file and saved it with replacing the older one but I had lost my new data which was entered in the old mde file?
Q: how can I make the new change on my original file and not lose my new data in my mde file.
View 3 Replies
View Related
Feb 4, 2014
So, I am inserting new records into a table. The values in the decimals are being stored as zeroes.
Field Properties
Long Integer
Standard (I also tried Currency)
2 Decimal Places
When I remove the "INSERT INTO" part of the query, the decimal data is in the results. I started out not using the FORMAT function. Then I tried it using "Standard", "Currency", and "#,##0.00"
View 3 Replies
View Related
Sep 19, 2013
I have a Union query as follows;
SELECT ALL *
FROM 1st_Lives
UNION SELECT ALL *
FROM 2nd_Lives;
1st Lives has 465,414 records and 2nd Lives has 151,852 records.
When I run the query I only get 604,976 records instead of 617,266 (I basically just want to add the two data items together).
View 1 Replies
View Related
Oct 15, 2012
What is the best way to do this without losing data? I have several FK that I made the mistake of setting up as lookup fields. I now want to correct this without losing data if possible.
DataType currently says "Number" under each of these. So I can't just change them to number. Is there another way to do this?
View 2 Replies
View Related
Jul 5, 2013
I have a form with a memofield (rich text). After hitting enter, a new line is added in, but the focus changes to the top of the memofield (of the same record).
View 6 Replies
View Related
Jun 11, 2013
I've got a table which lists accounts with an associated charge. If the account has multiple charges, then the account appears multiple times, for each charge. I'd like to consolidate the duplicates without losing each charge that is associated with the account.
View 4 Replies
View Related
