I've been generating a report using two forms for data, and everything has worked well until recently.
My report contains a field which tracks Therapist visits by date. Since the subform now contains multiple dates for each patient, the report prints a line for each date, resulting in two or three lines for each patient.
I would like the report to print a single line for each paient with the most recent date only. I've tried a fix suggested by another user in the Forum...(Top Value) in the query, but this resulted in only a single record being printed for the entire database. I then tried to add MAX(date) to the CRITERIA line for the DATE field, but now I get the message
"Cannot have aggregate function in WHERE clause". I'm stumped at this point, so any help would be greatly appreciated.
I am a student and I've just started to work with the MS Access and databases.
I am wondering if it is possible to filter the fields of table and display only specific information from that specific field. For example: The database (*.mdb file) is created automatically by the software (EPLAN Electric).
There are columns with fields like "de_DE@Verbindungsleitung;pl_PL@Kabel laczacy;en_EN@Connecting cable;en_US@Connecting cable;" But I would like to display in query/table filed only text starting from "en_EN@*" or "??_??@*" or display nothing...
I know that it is possible to display only rows which contain this text, but how can I get rid of fields from unwanted words...?
I cannot change original table of database because the parent-program (EPLAN Electric) will not recognize this database... And additionally data base is updated via EPLAN so every new field should be filtered in this way...
Tried to find things but being a mix of a couple of different actions havn't been able to actually find it
So basically I found a awesome form somewhere that builds the SQL query based on all the users selections.
When you hit the create button it will save the query and update the sub data sheet below with your query results.
From here the part I'm lost on is getting the TOP x number of records and moving them to a new table, I wanted it to work on the 2 drop down boxes that are on the form. select your values and hit the button.
I'd imagine it would be a sub query that I'd use for the top x like I have in the past but I just can't seem to get it to work
Quick steps
1. create your query 2. check your data in the sub datasheet 3. decide the number of records to move to the new table 4. select the name 5. hit the button
This would copy the ban, xcv & dfs fields from the importeddata table to the moverecordshere table & also update persname in the moverecordshere table with the name selected in the form.
I am new here and was wondering if anyone has ever had the following happen:
When you 'Filter by Selection' in a table for NULL values the number of records shown are less than the actual number of records with NULL values. For example, I tried this with a particular tables looking for NULLL records in a particular field and while I could clearly see that the number of NULL values was x, the number returned upon filtering on NULL in that field was less than x.
Any idea? Your help is very much appreciated. Thanks!
I have a project table that I track the progress of each job in by entering dates when certain portions are done. What I would like to do is when I fill in the "Project Completed" or "Cancelled" columns, to have that record automatically filtered out of the table. I still want to be able to turn off the filter at the end of the year to see all the projects we did, but I don't want this project cluttering up my table once it is finished or cancelled. Right now, I copy and move these completed projects to a separate table, but others say this is confusing and they can't figure out which table to use.
I input everything in the table format and don't have any forms that I use.
I have a table that lists all of my available seating for different events.
My table columns look like this: EventID - Section - Row - Seat - Available
Sample data in columns: 1 - A - A - 1 - Y 1 - A - A - 2 - Y 1 - B - A - 1 - N 1 - B - A - 2 - Y 2 - A - A - 1 - Y 2 - A - A - 2 - Y 2 - B - A - 1 - Y 2 - B - A - 2 - Y
EventID actually goes from 1-6 Section actually goes from A-F Row actually goes from A-J Seat actually goes from 1-36
When the user selects an event (or events) in the first box - I would like the query to eliminate the available seating, from the other events, in the results.
When the user selects a section (or sections) - I would like the query to further eliminate the available seating, from sections not chosen, in the results.
When the user selects a row (or rows) - I would like the query to further eliminate the available seating, from rows not chosen, in the results. And finally display whatever available seating is available - based on all three of the above criteria.
I purchased every access book I could find.. and I am slowly learning this. I'm a complete novice.. I'm not looking for any of you to do the work for me.. I'm more looking for guidence. I'm still getting to know all of the controls and their limitations. I have not yet written any VBA code. (It's still very confusing) I'm trying to get by on using the toolbox controls, using queries, macros and form controls.
Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.
This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.
I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.
How best to go about designing a new table that shows any records matching a specific user name field from a parent table (without changing any records in the parent table).
For example: Any record in the column "user name" matching "Craig" in the parent table should be displayed in the new table.
I also would like for the new table to update automatically anytime a new record meeting the user name criteria ("Craig" for example) is added to the parent table.
My database is centered on a main form where users select pieces of information from ComboBoxes (primarily) and submit.
The first part of the form, they must choose from a list of our clients, which is a table with with roughly 5000 rows.
Later on in the form, they must choose a contact person from that client. While Client's may have several contact names (which need to be stored in a table), only one name is necessary for the form portion.
How can I make this Contact Person ComboBox only load names attached to the specific Client selected above?
As it stands right now, the Client List has its own table (along with additional information), and Contact Name is a separate Table with a look up field of Client List.
I've created a report which has 4 subreports linked to it via a ClientID. The problem is it prints 2000 reports for the 2000 different ClientIDs. I want, went the button is pressed, for it to ask what ClientID the users wants to print the report for.
I know how to do this in a query based report via criteria, buthow to get this to work when the reports been directly made from a table.
i have about 14 fields that relate to subjects, basically i want to be able to run a query that searches for all the people who have choosen the module and report bak who they are..
now i know i can do this 14 times over but is there a way that the user can choose the subject first and the query will then run hence meaning only one query/report.. i thought of using a list/combo box but have absolutely no idea...
im very much a novice but any help would be most apreciated!
I have a query filtering question. Please note attach pic for further details. I am trying to put together a search in a form to seach for a certain color or color series from a Qery from two two different sources (from a Combo Box and from a Text Box). I am not sure how to set this up in the query.
Can someone please point me in the right direction?
OK, new problem to solve. I don't know if I am going about this the right way, so please feel free to make suggestions on better ways to acheive an outcome!
I have a query "qryBuildsPrinted" which searches a specific table for appropriate records, feeds these selected records into a report "rptBuildSlips" and prints them.
I am now trying to allow the user to print these build slips selectively, instead of printing out the whole lot pulled in by the query every time. My approach is to use the existing query, but filter the query based upon some selections made by the user in one of my forms. I am having trouble getting the query to filter successfully.
I realise that you can filter both the query and the report independently, so I probably only need to filter the report, but that is not working for me either!
Here is my code:
Private Sub cmdPrint_Build_Slip_Click() Dim intBikeID As Integer, intBuildID As Integer Dim varItm As Variant Dim ctl As Control Dim intCounter As Integer Dim intRecords As Integer Dim varArray() As Long Dim strFilter As String Dim blnPrinted As Boolean Dim msgMessage As Variant
'Print Build Slip for selected bike
'Set Control Set ctl = Me.lstResults intRecords = 0 intCounter = 0
'check if a selection has been made (kinda dodgy) For Each varItm In ctl.ItemsSelected GoTo Selection_Made Next
GoTo CleanUp
Selection_Made: 'Count how many records have been selected For Each varItm In ctl.ItemsSelected intRecords = intRecords + 1 Next
'Clear Array and Re-allocate Upper Bound of array (Array starts at 0) ReDim varArray(intRecords - 1)
For Each varItm In ctl.ItemsSelected
'determine BuildID of selected record intBikeID = ctl.ItemData(varItm) intBuildID = DLookup("[BuildID]", "tblBuilds", "[BikeID] = " & intBikeID) blnPrinted = DLookup("[PrintedSlip]", "tblBuilds", "[BikeID] = " & intBikeID) If (blnPrinted = True) Then msgMessage = MsgBox("One of the bikes selected has already had a Build Slip printed. Please adjust your selection", vbOKOnly, "Build Slip Already Printed") GoTo CleanUp End If
'Fill array with values, given how many values are present varArray(intCounter) = intBuildID
'Adjust counter to go to next array value intCounter = intCounter + 1
Next
'Select Filter String Select Case intRecords Case 1 strFilter = "[BuildID] = " & varArray(0) Case 2 strFilter = "[BuildID] = " & varArray(0) & " Or " & varArray(1) End Select
'Open query which displays the bike selected DoCmd.OpenQuery ("qryBuildsPrinted") 'DoCmd.ApplyFilter , strFilter
'Print report of query with filter applied DoCmd.OpenReport "rptBuildSlips", , , strFilter
A further question that is probably on the same topic is that once this has been completed, I also need to modify my existing Update Query which makes a modification to the data in the table. Is it possible to apply a filter to an Update query also?
I realise this may not be the most effective use of Access, so as I said, please feel free to suggest alternate methods I can research.
This query is to show aging. I have a field for processed date. I have a query that allows a date range to be selected using the Between function.
The query runs and pulls these variables from a form. It shows only those accounts which were processed between those dates.
However, it is also showing all of the accounts that have no date in this field. I need to set it up so that if the field has no date then it doesn't show those accounts.
I've tried Not (is null) for the criteria but that isn't working. I've tried a lot of stuff but nothing seems to work.
I have a query that when run asks for a client ID and then displays a total of funds for that client.
I also have a form for each client's persobal details and I would like to have a command button on that form, that when clicked runs the query automatically using the client ID being used.
I can get a command button to open another form based on client ID but not a query.
Dear Experts, I have created a database with queries that is working fine, however there are many records that have the same information that can not be removed easily, is there a method anyone knows of that can filter the records produced from a query to remove duplicates? i.e. 'select distinct' but for the whole query after the search parameters have been entered.
e.g. the search query is producing, after the refined search values are entered: 414353p - Fiat - 1.6 - MLS 414353p - Fiat - 1.6 - MLS How can i then apply a filter to automatically reduce these duplicates to just one entry for the whole form?
I have this form where there's mention of CompanyID
In a subform, I have the information about all the contacts of this Company. In order to see full detail information for a particular contact (of that particular Company) I have a Combobox with a query.
At least: that's the whole idea. Unfortunately it doesn't work in Access: SELECT Contact.ContactID, Contact.Name FROM Contact WHERE ((Contact.CompanyID)=(Me!frmCompany![ContactID]));
Can any help me? I need to do more of the sorts of queries... and I can't figure it out how to use values from forms and parent-forms.
I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.
I need a report which lists just the 'not approved' drawings. I've been able to add a query which lists the drawings but I loose the complete history, i.e. drawing recieved date.
Is there anyway I can display all the fields but only for those drawings which are 'not approved'
Im trying to develop a query which will show me players with the same 'player number' in a team.
I have two tables; players and teams.
Each player in a team should have a unique player number, so I want it to show records if eg Jim and Bob are both in Team X and they are both number 6.
I currently have this form (combobox) that filters using one piece of criteria.
Private Sub cboSelectPress_AfterUpdate() 'Moves to Press field and 'finds records where "press" matches whatever is selected in the combo box DoCmd.ShowAllRecords Me!Press.SetFocus DoCmd.FindRecord Me!cboSelectPress
End Sub
"Press" is a machine number. How can I add more combo boxes to filter additional information from my query. Like "room" or "product" or "date?" What happens if the user leaves one of the boxes blank?
My fields are
"Date"- date the product was ran "press"- the press it was ran on "product"- the name of the product "shift"- the shift it was ran on
I want the user to be able to select ALL or none of these fields for results.
I have a query that just shows all the records in a table. It is used by the end user for filtering primarily. Now the user would like to see a total for the amount filtered.
For example; the table is for repairs. The query just shows ALL the repairs. The user filters the client field to find all repairs for one client. He then wants to see what the total charges are for that query.
I cant create a new field and sum the records because it is not a totals query. Is there any way to embed the query in a form and use the form portion to sum the filtered results?
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table: CategoryID (PK) Description
Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID)
Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID)
JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID FROM Users, Jobs WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot! Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
1 table contains the records I needs to be queried. The result Im looking for is to view all the clients that have a different advisor assigned to a duplicated ssn. - not same advisors assigned to a duplicated ssn. Example of desired outcome. ssn name advisor plan 111223333 joe smith john doe b 111223333 joe smith jane sony c 111223333 joe smith unknown a
I am getting all data. ssn name advisor plan 111223330 Jack Jack john doe b 111223330 Jack Jack john doe c 111223330 Jack Jack john doe a
Ive tried query with using 'first' for the ssn and the advisor still give me all data. Please help me.... Thanks
I have a form named form1. In the form I have two unbound text boxes formated as general date; startdate and enddate are the text box names. In my query criteria for the ContactDateTime field I put the following code
Code: [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]
When I run it I don't get any results. So it runs but no records come up. I have about five queries that run when I click a button and I want to be able to just select start and end dates once on the form and have all the quieries run.
I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)
I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.
Right now on my column headings I have MONTH.
When I try to reference the form in the criteria, I always get the error
"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"
Does this mean that you can not filter crosstab queries?
