Hi guys. I am trying to help a client regards an un-supported access database. They have a DB that is shared across a network (only have experiance on database on single client, never set up security etc....). The DB is installed on the Server.
The DB is access via a mapped network drive. The Database folder contains the following files:
MKC Clients_Quesries.mdb
MKC Clients.mde
MKC Clients_.des
On one machine the MKC Clients.mde is launched and 1086 records are visible. On a second machine the same MKC Clients.mde is opened but displays only 977 records?? An I missing the obvious?
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details 2/ "Department" storing the departments of the unis we work for 3/ "Client" Name of the client(s) in that department.
The solution might be in front of my nose but I cannot see it... I have a query with all my records I need to extract only records of the current year, so that now will show all 2006, as we enter into the following year it will show all the 2007. What is the criteria I should put into the "Date" field I have? Thanks
1. Client Info table which contains the following fields: Clientid LastName FirstName
2.DatesDistribution table Clientid DateAttended
The relationship is one to many for these tables.I have been trying to create a query that will show the following:
Clientid LastName FirstName DateAttended (where this field only shows the most current date that the client visited)
Example: ClientInfo has 2 records:
Clientid=jsmith LastName=Smith FirstName=Jim
Clientid=tjones LastName=Jones FirstName=Tom
[code]...
I have tried grouping the records in Client Info table to get distinct names and using a Last function to get the most current date with little success.
I want to create tables with clients and another table to populate multiple orders for each client. How these tables will look like and how to create one to multiple relationship.
Firstly, I apologise if this has been posted before. I have done a couple of searches of the forums, but can't find what I'm after... and being new to access, I'm finding it hard to know what exactly to look for. How to even explain what I want is difficult!
I am designing a database for the clients we deal with a work. It started out as being a database of 'post' and correspondence, so the Idea was, we'd have a databse of all the clients... and by selecting their names, it would bring up a list of all the letters that have come in for them. I have managed this part fine - probably basic, but fine.
What I would like to do, is expand it to more than just 'letters'. Each client has done a particular 'work' with us, and each client has particular details that are unique. For example, a client will have made certain transactions through a bank account.
I hope to be able to click on a client, and have a screen or page pop up with details and comments about that particular client. I have tried hyperlinking but that doesn't really produce what I'm after. For example, I tried creating a "report" with a page for each client, so that I could have comments for each client - hyperlinked to the table. But all it does is link to the "whole report" and not a particular page for a particular client.
I'm sure this is sounding really confusing: I have a client named "Peter Litman". His name and details are within a "client" table (listing all clients). I hope to be able to click on his name (or row) and bring information (comments, details etc.) about him. I thought this could be some sort of "pop up", or link to a special page. ??
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code: Private Sub CmdAdd_Click() Dim dbAddClient As DAO.Database Dim rstAddClient As DAO.Recordset Set dbAddClient = CurrentDb Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
I have 4 tables and around 440 records but can only view up to 417 in the form I have designed. I have been adding new records via form and it has been added to my main table, but when i try to view it in form view - the record is not available to view. What do I need to do to correct this problem?
I have checked that there are no filters, data entry is set to No, Auto deletions, additions and edits are set to yes.
Also to mention it seems that the problem has arisen since I set up some new queries, there is a one to one relationship between the tables!
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
I am trying to put together a database that tracks individual clients and what products they selected for a finance company. I have setup the CLIENTS list but because each client will have different (and multiple) services I wanted to add the SERVICES list as a separate item and then link each product to the individuals that each record relates to. There may also be two clients on the one product so again it would be ideal if the product (i.e a Home Loan product) was setup in the SERVICES list, and then I could link One or Two clients to that individual mortgage record using their data which is stored in the CLIENTS list.
if there is a VBA command that returns the current view of the current active report. I am running code the uses the SetFocus Action, which works fin in Report view, but when I try to go to Print Preview view, Access throws an error stating that the command or action is not available in the current view - and the GetFocus action is the culprit.I can skip the GetFocus action if I know the Report is in Print Preview mode.
hi i have 3 queries brought together in 1 form. i do not want a report to be printed out just the current record in form view. this is because i have a number of images per record. if i print out the current record and it only has 1 photo in a sub-form attached to the main form record, then the main form will print on 1 page which is what i want [screenshot 1]. But if there are 2 or more photos attached to the main record then the print command button prints out 2 pages for 2 photos, 3 pages for 3 photos and so on [screenshot 2]. any ideas as to how to limit the print command button to print out only 1 page per record regardless of how many photos their are attached to each record?? thanks
Hi, I am in the process of creating a new database - I am a bit new to this. I have a main form, which also contains two subforms. The record source of the main part of the form is 11 tables, and then each of the subforms is based on one table each (so there are 13 tables altogether).
Everything seems to be linked up ok, and when I add new information to the form (and the subforms), the information gets saved to the tables as it should be. However, I want to be able to view past records in the form view, so that users can regularly update past records using form view. I can't do this. Each time I open the form view, the thing at the botton left says 'record one of one.' THen if I put the curser in (for eg) the box called 'client name' and click on the search button, I get a message saying:
"you can't find or replace now, the fields are not searchable due to: - the fields are controls; - the fields have no data; - there are no fields to search."
well I don't really know what that means. There is definately data in there somewhere, because I can view it in the table view, but I'd like to be able to view and update old records, and search, in the form view.
Hope you can help and I'll explain this as well as I can but I've put it in general as it's a culmination of things.
I have written a call monitoring database for a call centre (all good so far), but they want historical searches (again all good). I've written the relevant queries for these so that the searches can be done for Adviser, Manager and also by a date range. However, they now want to be able to print out the current search from the screen.
The way I have done it is this. I have written the report be adding in a subreport and putting in the relevant historical search form, and then written a macro that is assigned to a button on the historical search form to print the current record.
The problem that I am having is that when they click on the button to give them the history they fill in the relevant advisor or manager name and the date range which gives them the info in form format but, when they click on the button to print the current 'screen' if you like, it asks for the information again. I know this is because the actual report is written from the same query but is there anyway that it will just print what's displayed on the screen? I don't do VBA so if it has to be done that way could you please display it as it should be inserted.
I do hope that that has made sense and that someone can at least help me, cos you're all wonderful and I've had some really excellent help in the past from this site.
As today being the 1st October, I would like the query to return the values for Oct and Nov. The same values would be returned on the 31st Oct, but then the following day, only the Nov value would be returned...so, basically, it only returns the values of the current month and following months..
I am assuming I put something in the criteria of the query under the month? I have tried a number of things, with none working!!!
I have inherited some Foxpro tables which I am trying to convert to Access and I hae set the exported tables up and have tried to set the relationships between the tables so that I can run queries and setup forms to review and add new records. However I am struggling to get the relationships in place that will allow me to do this. When I try to drag the common fields from one table to another to setup the link I get a screen which shows Relationship Type as 'indterminate' rather than 'one-to-many'. Whatever I do I can't change the type of link and when I view a query based on the two linked tables the * in the record navigator is greyed out so I can't add new records. I have a sample of the tables,links,queries and forms I have setup to date (with a limited number of records), if someone could have a look at it and advise where I am going wrong that would be great. The data and the structure of the tables is pretty much fixed as this is what is coming through from Foxpro and there is a lot of historical data to bring across. Many thanks
I have a db that is a checkbook register. I use a field in each transaction record to tell whether the transaction is current or is a future transaction (if it is in the future then it doesn't yet affect my balance). For instance, if the item is not scheduled to be paid for two weeks (I schedule payments with online banking), I check the "future item" box, then that record for that item isn't included in the report that tells me my current balance. How can I automatically take away that check mark on records when the date in that record becomes todays date (or before today)? Because at that point it is no longer a "future" transaction but is current. I could do this manually but it seems like there should be a way to do it automatically.
Another problem has thrust its ugly head into my personal universe.
I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.
I have created one query that grabs each of the relevant personnel;
SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS FROM dbo_personnel WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#)) ORDER BY dbo_personnel.originalapptdate;
I've saved this query as "2-yr Service Check Baseline."
And then this query that does the count;
SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS FROM [2-yr Service Check Baseline] GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");
The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.
So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.
But, in the report, how do I reference a field from a record other than the current one?
Just wondering if anyone could help me with my little problem :)
I am making a register (as in a class register of students) I have a table with the lessons on and one with students on. I have made a relationship between them etc.
I have a form where the parent allows you to choose which lesson you want to view, and the subform displays the students for that lesson. This all works fine.
My problem is that I also want a picture to be displayed for the student that is currently selected, I have managed to update the picture by using
Private Sub Form_Current() [student_picture].Picture = [student_image] End Sub
student_picture is the name of the image on the subform, the student_image is a row in my student database that contains the location of the image e.g. c:abc.jpg
and this works at updating the image that is show. The only problem is that the image is shown on all records/rows. Is there anyway of making it so that the image is only displayed on the current row?
I have been reading quite a bit on this and other forums about email automation and looping queries to gather email addresses. However, I am unable to restrict my list of email addresses to only that are currently being viewed in a subform.
An image of my form is attached. I have 3 tables being used:
Events Contacts EventAttendance
The EventAttendance Table is a join table to determine which Contacts attended which Events.
When I try to loop a query to capture email addresses, I am only able to return email addresses for the entire query (all people attending all events) not just those that are listed in the subform for the current event.
How do I adjust the code below to only use the emails that match the EventID I am viewing?
Private Sub Command19_Click()
Dim rs As New ADODB.Recordset Dim strEmail As String
Hi, this problem is probably very simple to solve but I can't seem to find an answer to it.I have created a search form that returns all records meeting the criteria back into the search form. What I want to be able to do is to display the records in a datasheet view. This is what I currently have and it works ok but how do I convert?Private Sub cmdSearch_Click() Dim LSQL As String Dim LSearchString As String Dim LTownString As String Dim LActive As Integer Set LSQL = Me.RecordsetClone If (Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True) And (Len(txtsearchTown) = 0 Or IsNull(txtsearchTown) = True) Then 'MsgBox "You must enter a search string." Else LSearchString = txtSearchString LTownString = txtsearchTown Select Case Me.Frame99.Value Case Is = 1 stActive = " AND Active = -1" Case Is = 2 stActive = " AND Active = 0" Case Is = 3 stActive = "" End Select 'Filter results based on search string LSQL = "select * from tblContacts" 'LSQL = LSQL & " where LastName LIKE '*" & LSearchString & "*' AND Town LIKE '*" & LTownString & "*'" & stActive If Form_frmMain2.RecordsetClone.RecordCount = 0 Then MsgBox "No records found" Else Form_frmMain2.RecordSource = LSQL End If 'Clear search string txtSearchString = "" txtsearchTown = "" End IfEnd Sub
I have 2 tables that have the same structure and same column names - Table A and TAble B. If field1, field2, field3, field4, and field5 match - I want to see that duplicates exist.
How do I do it with 2 tables? I know the query wizard check for duplicates can do it in one table - but I need to compare the two.
i have an access database which is queried by my ASP script. now....i want the table to only return records which are equal to "=" or higher">" than todays date.
I have done this before using queries in access, but have since forgotten and lostthe code.
I know it is very simple, but i just cant remember.
Id rather query the date in the database table than the code for other reasons.
