Pulling My Hair Out...
Feb 6, 2008
I'm certainly not new to Access, but this is the first time encountering a problem like this. I have a table with 3 Field Names, [Return], [Header] and [Date]. An example table looks like so..
Return Header Date
1 | 10 | 1/1/2007
2 | 10 | 3/1/2007
3 | 10 | 5/1/2007
13 | 20 | 6/1/2007
14 | 20 | 7/1/2007
I need to take to have group the Header's together, for instance 10, and have it return the [Date] for whichever line has the lowest [Return] value. I just can't figure out how to do it. Any help or insight would be GREATLY appreciated. I'm 90% done with the project, and this last part will seal the deal.
View Replies
ADVERTISEMENT
Dec 28, 2006
I have created several Queries and I would like to have a column that numbered the results. I have looked all over saw a couple of samples but havent had any luck. Anyone have any suggestions. Thanks for your help and have a good New Year.
Just to clarify basically this is what I need. Loan Number is the Primary key.
# Column Borrower Loan Number
1 smith 123456
2 jones 023567
3 jang 102569
JW
View 2 Replies
View Related
Feb 24, 2005
I have a database from which users e-mail country specific reports to a group of receipients.
I am currently using the following code:
DoCmd.SendObject acReport, "Report UK", "SnapshotFormat (*.snp)", "rec1@company.co.uk;rec2@company.co.uk;rec3@company .co.uk", "", "", "Report UK", "See attached document", False, ""
Everytime when a receipient needs to be changed or added the users ask me and I have to change this code; this is offcourse not the best way. For some countries I even have as much as 15 recepients
What I would like to do is set up a table showing for each country the e-mailreceipients, link this to my form and use the field from the form in my code which is activated by a click on a button.
So I started testing this, but I am really breaking my head on this one....
I found two other examples on e-mail from which I took some code and sort of got something working.
The problem that I have now (see attached database) is that even though there are e-mailaddresses in the field on the form the code considers the field empty (null) only after clicking on the field the script works.
My idea however was to make this field a hidden field - users should no be bothered about this
Anybody have an ide how I can overcome my problem?????
View 2 Replies
View Related
Jul 16, 2005
Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.
1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.
2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.
3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.
I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.
-Jim
View 3 Replies
View Related
Sep 22, 2007
I have a data source table with names, addresses and UK postcodes, and another table that has partial postcodes (the bit before the space) and I need to look up the local authority for each postcode. I created a front end that links to both tables and tried converting the full postcode in one table to the first 4 characters and the partial postcode in the other table to the first 4 characters too, using two separate queries, both of which give the right results. Then I used a third query based on both of the above to match up the records from both queries, but I get a "Data type mismatch in criteria expression" error. I am just using queries on tables because I'm not very au fait with VBA and so on. Any ideas, anyone please? (I can happily email all to anyone who wants to look.)
View 4 Replies
View Related
Mar 3, 2005
Hi All.
Before I start, I have read other posts on relationship, but none that can answer this, so if it does, maybe someone can point me in the right direction.
I have put together database's before, but just with parent to child relationships. I need a db to go further.....and have been trying for about a week, just going round in circles!!
e.g.
I have the following tables...
tblProject
tblBuilding
tblFloor
tblroom.
I can create it so there are many buildings in each project, that is OK. I need it so there can be many floors under each of the buildings, and many rooms undereach of the floors etc etc etc.
Also, need a Unique_ID for every record of building, floor, room.
I will need in the end to run a report on a room, which will tell me the floor, building, project it is in etc.
I cannot work out the relationship's between each table and, I know I can insert a subform into a form which will link tblProject to tblBuilding, but how do I add tblFloor to that link?
I do hope this makes sense, and I have attached a db which may help explain
Many thanks in advance.
Frank.
View 1 Replies
View Related
Feb 6, 2006
Hi,
I have a split database. The front end with forms etc is distributed to users and the back end with tables sits on a shared drive.
I have been trying to set the security on this and quite frankly it's driving me mad. I managed to get the security set with 5 user groups and about 100 users - it all worked well on my PC but when I distributed it, the copies open with the Admin as user, no password required, resulting in no security whatsoever.
Further reading showed me that I had to change the owner of all objects from Admin to a new adminitrator, who I named Custodian1. This I did. I then created a new database while logged on as Custodian1. So, the new database has all objects owned by Custodian1 and the database itself is owned by Custodian1.
The book said I had to delete the Admin user but, after asking if I was sure that I wanted to delete the user, Access would not let me.
I went through the Workgroup security wizzard a number of times to try to reset things that way, all to no avail. I then did the whole ownership change thing with the back end database and relaunched the wizzard. Still unable to delete the Admin user.
My final attempt was to use the wizzard and create a new WIF rater than edit the existing file. And still, I cannot delete the Admin user and distributed copies have no security.
I don't know how this ties in, but the distributed copies do not seem to be linked to the WIF although they find the Back End database, in the same shared drive/directory with no problems. If I set a password on a distributed FE database copy, it then cannot be opened as the users are not defined.
I'm obviously missing something fundamental to the whole process. A further difficulty is that I have the live database FE/BE, working copies FE/BE, The security wizzard backups FE/BA, and all the directories have similar names. On top of that, I have my normal none database duties to distract me. I'm finding it very hard to keep track of where I am or what I am doing.
Any help most gratefully appreciated and received.
Regards,
Keith.
View 14 Replies
View Related
Jun 18, 2006
Hi Guys,
The asset managment system I have been developing is due to go live next week and I am really struggling with the data entry and search forms.
I have attached a picture of the main form (and outlined each subform) to give you an idea of my project.
The attached form is actually a Main Equipment Type Detail form with a child subform AssetMain which has a number of child subforms i.e. Asset Details, Purchase Details etc.
When a user clicks to add a new asset a popup form asks them to select an equipment type i.e. PC. When they click it opens the attached form. Their selection filters the equipment type detail values in the combo i.e. Laptop, Tower, Server etc.
When a value is then selected in the combo you can then add an asset for that equipment type detail. (One Equip Type > Many Equip Type Detail > Many Assets)
1st Problem)
I have set the Asset Main subform to allow additions only. When the whole form opens, the Equipment Type combo (at the top) does not display a value but does actually contain the first value.
How do I force my user's to select a value in the combo before it actually filters the child subform?
2nd Problem) The Asset Main Subform (filtered by selected Equip Type) should only be used for data entry. I set the data entry property of the form to True but when the value in the Equip type combo changes, it displays all the relating records in the subform. My only solution so far is to stick
Subform.DataEntry = True
in the Equip Type combo's after update event. However, this doesnt look to good as it flashes up with the data and then displays a blank record.
Any ideas?
3rd problem) Due to the fact that I am using a number of subforms, my tab order is such that values are entered for the main form (i.e. ID, Condition) then subforms (i.e. manufacturer) then back to the main form (i.e. allocated to). This means that:
a) I am having problems validating the record beforeinsert as I have had to change the required property to "no"
b) Once the user has tabbed through all the forms what is the best way to go to a new record? Alternatively if I add a new record button I need to check that the necessary fields have been filled in (on all of the forms/subforms).
and finally the 4th)
I have a search form that queries the assets. The search results are displayed in a list box. When you dblclick the list box I need to show the asset details.
Question: Can I use the above form to do this? My only problem is that I need to open the AssetMain form which is a sub form in the above. If I try and use the existing form it will not open as many of the controls refer to the form as a subform. Do you think I need to create a separate form for showing the data to entering it?
Would really appreciate some help (not that you havent heard that before :) )
My head is going to pop in a minute!!!
Thanks
Ben
View 5 Replies
View Related
Nov 7, 2005
I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.
View 1 Replies
View Related
Dec 28, 2006
How could I pull in someone when they do not have data?
There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.
View 1 Replies
View Related
Aug 2, 2007
I'm trying to figure out how I can pull the most recent entry in a table for each of the properties in our database. Let me give you some background. This access database is used to format reports from an SQL database - all of the tables are linked and all of my other reports work great except for the one I'm trying to create from a table that gets updated every day by way of a stored procedure that runs every night on the SQL server. In my mind this shouldn't make a difference and I should be able to select whatever data I want from this table/query based upon whatevery criteria I want. The field in the table I'm trying to use is a "date stamp" of when it was updated via the stored procedure. I want the last entry made for each property so I selected "last" as the criteria which should get me approx. 20 lines - one for each property. But when I use this I still get back multiple entries for each property. This seems like it would be easy to do but everything I try doesn't seem to work.
That's why I've turned to the experts here for help.
Thanks
View 4 Replies
View Related
Oct 31, 2006
I have a field that is updated with =Now()
now when I try to run a regular query with the criteria being
=Date()
I get nothing
And the "=" is always disapearing out of the query.
I dont know what is going on. I got the field set up in the table section as a date field with short format.
Can somone please tell me what I am doin wrong when I have followed instructions up to this point....
View 6 Replies
View Related
Mar 11, 2007
Hi, I was wondering if someone could check this code over. I am trying to pull a query (titled: Codes_+_Conduct:_General)which was already created in Access. Here's the code: (the red is where the error message I get is)
-----------------------
<%
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Codes_+_Conduct:_General order by subject asc"
objRS.Open strQuery, objCon
IF rs.EOF Then
Response.Write("<tr><td colspan = 3>No Tips</td></tr>")
Else
Do While not rs.EOF
x = x + 1
%>
<strong><%=rs("subject")%></strong><br>
<strong>Q</strong> <%=replace(rs("question"),vbcrlf,"<BR>")%>
<br><br>
<strong>A</strong> <%=replace(rs("answer"),vbcrlf,"<BR>")%>
<br><br>
<%
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set objCon = Nothing
%>
------------------
The error message I receive is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/faqs2.asp, line 13
If anyone could offer me any help, I would greatly appreciate it. Thank you.
View 4 Replies
View Related
Dec 11, 2005
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
jon
View 1 Replies
View Related
Jan 23, 2006
Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?
View 3 Replies
View Related
Feb 12, 2006
I have a column of data set up as follows:
Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.
I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.
Thanks
View 5 Replies
View Related
Dec 10, 2007
Hello all,
I would consider myself a novice to almost intermediate with access.
Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.
I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...
Thanks for your time!
View 1 Replies
View Related
Apr 13, 2007
Hello Everyone,
I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?
Thanks,
Pablo
View 13 Replies
View Related
Jun 20, 2007
I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?For example..My made table has20120220321Q24Retc...the teradata table shows this201G101201G102201P202203A12021QP131the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!
View 14 Replies
View Related
Aug 10, 2007
I am having an issue pulling information from a form into a query. In my criteria column in the query i have:
[Forms]![frm_createcsv]![txtCriteria]
it pulls from a valid string from a textbox txtCriteria.
My vba code is:
Dim query as string
query = "pkg_not_registered_email"
filename = "C:querytester" & Format(Date, "YYYY_MMDD") & "_" & cboCountry.Value & "_" & ".csv"
DoCmd.TransferText acExportDelim, "", query, filename
DoCmd.Open query
____________________
This exact code worked previously with another function and a different query so I do not understand why it is not working now! Help! :(
View 7 Replies
View Related
Dec 17, 2007
I've linked access to an external database and created a query to pull results from the linked access table that was between certain times on certain dates (i.e. between 11/12/2007 11:01 AM to 12/13/2007 12:01 PM). The query I have thus far is "SELECT * FROM TABLE1 WHERE DATETIME >= #11/12/2007 11:01 AM# AND DATETIME <=#12/13/2007 12:01 PM#". That works, but I want the date and time to be changeable through text fields on a form. Something like "SELECT * FROM TABLE1 WHERE DATETIME >= #forms.form1.startdate forms.form1.starttime# AND DATETIME <= #forms.form1.stopdate forms.form1.stoptime#". The dates are going to be automatically generated according to other parameters but the times are going to be entered manually. How can I accomplish this?
View 1 Replies
View Related
Jan 25, 2008
Here is my issue I am having, With the records we are pulling it is not pulling everything from the previous 24 hours. It is using the "D" to pull the information, however it is also using the default system time, so if i have a record that occured last night at 8:30 pm it is not showing up.
How do i get all of the records from the last 24 hours regardless of system time?
View 14 Replies
View Related
Nov 14, 2005
Hi,
Here is my problem. I am trying to use a form to enter data that is contained on one table, have it pull data from another table that is related to the original data, then add data and dump the whole thing off to another table. I would like to do this one item at a time. The only way I can get this to work is to pull all the data at once into the form. Is there any way I can pull just the related data I need by just entering the item number?
Thanks,
D
View 1 Replies
View Related
Jun 22, 2006
I've got a form for a volunteer tracking database I am trying to create that has all the voluneer's infomation and has a subform in it that shows the volunteer's activies and hours. I have a text box (txtHours) on the subform with the following Control Source "=Sum([hours])", with the visible property set to no. This sums the total hours volunteered by the individual.
On the main form I have another text box (txtTotal) with the following control source "=Hours.Form!txtHours" where:
Hours is the Name of the subform and the Form!txtHours references the control on the subform.
I am trying to create another textbox that will display the total hours for all records in the form, but not having any success.
Any suggestions?
View 4 Replies
View Related
Jul 7, 2006
I've made the form below, with a sub form in it to show the table below the text boxes. What I'd like, is for the user to be able to select a record on the table (preferably just by clicking on it) and the text boxes to automatically populate with the relevent data. Is there a way to do this?
http://i73.photobucket.com/albums/i204/tomjamieson/frm.jpg
View 4 Replies
View Related
Sep 9, 2004
Hi
Apologies if this is really basic - I am trying to write a query to show all records from table A linked to table B so that if a field is blank in table B, it will show. At the moment, every time I run the query, it only shows me records which have entries in both tables. (They are linked by ID no).
ie there are 77 records in table A. Only 23 in table B but I want a query showing which ones in table A do not have something in field "x" on table B. Does that make any sense ? As ever, I'd be ever so grateful for help.
While I'm at it, does anyone know anything about getting a database to produce reminders ie based on date entered, highlight when 3 month review is due ?
Thank you thank you
View 5 Replies
View Related
