Hello everyone I am an access novice in seek of some help,
I'm trying to add a check box to my customer table and then query all the rows that have been checked. How do I go about doing that?
I've put together a very generic database of how I would like to do it. Including the table, form, and query.
I'm just stumped on this issue for some reason.
Thanks for your considerations,
Nick
I have two four tables. Invent, trans, NewOrder and reorder tables. The new order table is not joined by a relationship unlike the others which are joined with the Invent ID field.
The new order table has the fields(ID,QTY,NAME,Order Date). I want to use this to order new items that are not in the Invent table. What do I do to check that I don't enter a new item name in the NAME field that is already present in the Invent table?
I have been creating a Access database to hire bikes out. I have created all the tables etc but i am stuck creating a query i want to create. I want to have a sub form on my booking form from a query which will show all the available bikes that can be hired. I have looked at the lending library sample and this hasn't really worked, My database is set out differently.
I have attached a version of the database to the post, this is zipped with some screenshots which may be useful.
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
Hi I'm having a bit of a problem with the sql query below
SELECT [Table 1 Personal Details].Surname, Sum([Table 3 Daily Activity].[Public Holiday]) AS [SumOfPublic Holiday], [Table 3 Daily Activity].[Week Commencing], Sum([Table 3 Daily Activity].[Authorised Absence]) AS [SumOfAuthorised Absence], Sum([Table 3 Daily Activity].Holiday) AS SumOfHoliday, Sum([Table 3 Daily Activity].Sickness) AS SumOfSickness, Sum([Table 3 Daily Activity].[Unauthorised Absence]) AS [SumOfUnauthorised Absence], Sum([Table 3 Daily Activity].[Hours Per Week]) AS [SumOfHours Per Week], [Table 9 Funding].Project
FROM [Table 1 Personal Details] INNER JOIN ([Table 3 Daily Activity] INNER JOIN [Table 9 Funding] ON [Table 3 Daily Activity].[ID Number] = [Table 9 Funding].[ID Number]) ON [Table 1 Personal Details].[ID Number] = [Table 3 Daily Activity].[ID Number]
GROUP BY [Table 1 Personal Details].Surname, [Table 3 Daily Activity].[Week Commencing], [Table 9 Funding].Project
HAVING ((([Table 3 Daily Activity].[Week Commencing]) Between [Enter Start Date] And [Enter End Date]) AND (([Table 9 Funding].Project)="NETWORKS"))
ORDER BY [Table 3 Daily Activity].[Week Commencing];
My problem is that the Surname is displaying for each record.. what I mean is that I want the surname to be grouped so that I get the SUMofHours summed correctly?
here's an example of what I'm getting
SURNAME, WEEK COMMENCING, SUM OF HOURS, PROJECT
Brown,13/02/2006,35,NETWORKS
Brown,20/02/2006,35,NETWORKS
Malcolm,13/02/2006,35,NETWORKS
Malcolm,20/02/2006,35,NETWORKS
Here's what I'm really looking for though
Brown,13/02/2006,70,NETWORKS
Malcolm,13/02/2006,70,NETWORKS
I need the Hours SUMMED with no replication of surname.
Thanks
CJ
Thanks
CJ
how can i make a image appear in my form when there is a check in the check box from the table?
View 14 Replies View RelatedHey all,
Here is my problem, im working on an old 97 database that is being used by a program and sometimes errors occur through the use of the program and certain tables are not updated properly, so instead of doing this manually I want to be able to query this database through SQL and then later down the line put these SQL queries into a program so it will be more user friendly to edit the database.
So when I try and test one of my queries out, access tells me that I cant make changes to the database because it was created in an earlier version. Now I know i can update the database, but if I were to do that the program that is using the database wont work!
So im a little stuck now, will I have to keep editing the database the manual way or is there another option here? Im wondering (for any java heads out there) If I were to write a java program that would query the database and edit the data inside would that work or would I get back an error?
Thanks in advance!
Ok, I have a DB of client contacts. I'm drawing the values for a query from a form using [Forms]![Sort]![Contact] in criteria and using the parameter field to give values to each. The query works fine for state, contact and industry- all in conjunction with each other. Where I am having problems is:
1) When I input less then all 3 working parameter values in the referenced form the query will not run. I think i need an SQL string of some sort- doing an if, then relationship, but I don't know how to do this.
2) I don't just have 3 values I want to reference, I have 9 total. On the form one of the problems is that a field is chosen by the end-user using check boxes (allowing only one check between three values, with no default: current client, potential client, not applicable are the choices). However, in the table these are referenced by the values of 1, 2 or 3. Another of my fields is check boxes, but referenced by yes, no. How do I correspond the field values in text to numerical values in the table? (and the yes/no prbly the same I will assume)
3) I have "sectors" as a field option in my Sort form (again the drop down menu to select). There is one field for the choice, however, in my data table I have 4 fields corresponding all back to that one field in the form through the query. This doesn't seem to work at all. The sector choices, in my original Input form, are 8, chosen and recorded into the table using 4 consecutive drop downs. They are the same 8, so sometimes a sector like Agriculture could end up in the Sector 1, or Sector 2, etc field in the table. Some can also be left blank. So in summary of this dilemma: 4 drop downs in input form to 4 data table fields, all being queried through one field on the Sort form, which is tied to my query through a command button, having specific criteria reference text in the form.
Thanks in advance for any help!!!
Just joined this forum and posting my very first thread.. ^^
I need to create query to pull data from two different tables. This should be really easy query for most of you. Once someone help me set it up, I can do the modifying.
If you look at the image, there are two tables. For each of pf_id (which is unique ID), I need to know the dept_name which is on other table. I know two tables can be linked with dept_id but I don't know how to create query. If you can help me , I would greatly appreciate it~
Result should be like below.
pf_id, dept_id, dept_name
___________________________
wh08051bh-Navy, 01-001, washed hat
wh60516bh-white, 01-001, washed hat
.
.
.
ru11072bh-white_with_navy, 01-002, roll up bucket hat
.
.
dh03871bh-Dk._Blue, 01-003, denim hats
.
.
<img src="http://www.highdots.com/forums/attachment.php?attachmentid=183">
http://www.highdots.com/forums/attachment.php?attachmentid=183
Is there a way of querying for an astricks? Some data in one of my tables has data that looks like this "A*" or "B*" or "C*", but most of the records are just "A" or "B" or "C"...I just need a query that I can pull out those records. Is there a way of doing this in a query?
Thanks
I need to take a value from a subform and use it in a query but I don't seem to be able to come up with the correct criteria in the query....
I have so far tried:
[Forms].[frm_Matching].[SubMatching].[txtSingleFixedPayer]
[Forms].[frm_Matching].[SubMatching].[form].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[form].[txtSingleFixedPayer]
[Forms].[frm_matching].[sub_matching_single].[txtsinglefixedpayer]
[Forms].[frm_matching].[sub_matching_single].[Form].[txtsinglefixedpayer]
And probably a load of other versions of the above.... can anyone help out possibly?
The following pulls back the value in code but isn't accepted in the query as a valid criteria:
Forms("frm_Matching").[SubMatching].[Form].[txtSingleFixedPayer]
OK,
I work for a political party. I work with voter history information. Normally, I am doing queries in order to bring up particular voters, and eliminate others.
Example - the basic table I work with contains the names, addresses, affilitations, and voting histories of everyone in my county. I normally query to bring up voters of a particular party, or people who voted in a particular election or number of elections or both. I more or less sort voters out, but in a positive manner, by creating tables with voters who did a particular thing.
For example: out of all the voters, I just want Democrats who voted in two of the past three primaries. I would then get a table that contained just those voters and no others.
Today, though, I was asked to create a table that would exclude voters based on their behavior, exluding voters who did a particular thing but showing everyone else.
Example, I have a request for all of the voters in the county, except those who voted in the 2004 and 2002 primaries. I know how to create a table with those voters and no others, but I don't know how to query for everyone but those voters. Can anyone help?
Hi there,
I am getting really wound up with this. It should be so simple!
I need to create a query on a field (actualdateofvisit) and group it by the month to give me a count of all those visits (hence the catchy field name) which occurred each month since the beginning of the project. In other words I want to see:
Month Count of Visit
Jan 43
Feb 54
Mar 78
and so on.
I have tried everything I can think of but cannot work out how to do this? I need to do it in a query NOT a report as I want to port it to Crystal reports to display there.
I am think of something like:
field: Actual Date of vist
table: visits
total: Group By
Criteria: "Month"
Can anyone help me?
Thanks and Regards
Mark
Hi all,
I have a query that is supposed to get a time value from a field and enter a string into a new field based on the time it gets.
this is what i have. Its my VERY first query ever and so i'm not sure of the syntax. I doesnt work though so i'm assuming something must be wrong. Can anyone help?
iif((hour([request date])>12)and(minute([request date])>30)), "After 12:30", "Before 12:30"
Cheers,
Spinkung. :)
Hi all,
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
Any help would be greatly appreciated.
Hi, I'm having some issues querying a sub form.
I'm using a combobox (in a subform) which supplies some search criteria. I'm using the query Like [Forms]![Employee]![Course Subform].[Form].[Combo12] & "*" but its simply not working.
Can anyone see any glaringly obvious mistakes?
I hope someone can help with this query I am having difficulty with. The table I am querying has several expiration dates and Yes/No Fields
Listed below is what I need and the query needs to return the name and data if only one or all of the following applies
Name
"A Date" is before today
"B Date" is before today
"C Date" is before today
"D Date" is before today
"A Yes/No" = No
"B Yes/No" = No
"C Yes/No" = No
In other words, if all dates are = to today or later and Yes/No Fields are = Yes - I don't need them in the results.
I have tried this several ways using AND and OR but either way my results are ALL the records. not just the records that meet any or all of the above criteria. Maybe it's too much to ask? :o)
Thanks in advance for your help.
Terry
Hey.I know you can create queries in access to find fields that have a value between 3 and 7 in them for example but how would you create a query that retreives fields with values between 2 dates for example between 12/10/07 and 11/01/08Btw the dates are inday/month/year
View 1 Replies View RelatedI need to select the last 20 records from a databse that have over 1.25 MILLION records.
Obviously the usual stuff like order by is breaking the server with buffer issues.
Anybody any ideas of how to get around this?
Hi everyone,
I have a DB that has over 180,000 records with zip code as one of the fields.
I would like to know how do you query the 1st 3 characters? For example, if zip is 95127, I want to query and retrieve records for the first 951.
I seen this on another thread, but can't find it. THanks in advance guys.
Hey all.
I'm trying to get a combo box to populate the box next to it.
Basically pick a project number in the 1st box and have the project name show up in the second box.
I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.
Here is what it's doing:
http://img156.imageshack.us/img156/2659/dbhm4.jpg
Here is the code im using.
Private Sub ProjectNumber_AfterUpdate()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
ProjectNumber.SetFocus
If ProjectNumber.Value > 0 Then
strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProjectName = rs("ProjectName")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
Let me know what I can do.
Thanks!!
I have a database where the primary key is a field for pass numbers. Many of the pass numbers begin with zeros (example: 0023456). I changed the table property for the pass numbers to text so the zeros would be recognized. However, I have a form based on a query to search this pass number field. How can I get the query to recognize the pass numbers that begin with zeros. When I put in any other number above zero, the pass number satisfies the query and the employee information pops up. Aside from AllowZeroLength and trying to format the text field, I cannot get the query to recognize the pass numbers that begin with zero. Please help:eek:
View 3 Replies View RelatedPlease can anybody help.
I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in.
I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys.
This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in.
The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.
I know I can do this if I have a separate record for each member in the form
but I really need ten members in each record.
I hope this makes sense and somebody can help.
Thanks in advance
John
I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
Thanks for any ideas
I apologize in advance if this question is completely obtuse, but I'll pose it to the group nonetheless.
My task is to analyze accruals versus actuals for the past quarter (i.e., July-September). What is of particular interest are the variances between the accrued and actual amounts. I have several fields of data, but the common field is Shipping Unit (SU). My initial plan is to upload the tables with the accrued and actual records separately and query SU's to pull in all instances in which there are matching accrued and actual SU's. This is where it gets a bit fuzzy for me. I would export the resulting queries into Excel, unless there is a means to produce the analysis directly from Access.
I haven't used Access in ages, so I don't recall how I might best proceed. Please advise with your thoughts. :confused: