The fields are somehow related where values of B (unique) is taken from A(unique).
I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output:
A B
9 6
6 5
5 3
3 1
1 Null
Or in another case where I input a single value A=8 or 4 or 2
the following should be the output:
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
I am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!
this query works = SELECT * FROM Sample_Data WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));
but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.
I'm sub-novice when it comes to Access I'm trying to get calculated data in a field in a sub table to autofill in a field in a related table. So all of the data in 'total hours' column is calculated by the variable inputs in the 'hours' fields. Once there, I want it to feed itself into the 'total hours' field on the main table. Is that possible? Also - the 'total hours' field in the sub table won't calculate anything unless a value is placed in EVERY 'hours' field in the same sub table (e.g. 0).
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
I have a combobox on my form with fields related to it (ie. when an option is chosen in the drop-down, the next two fields are also populated with data based on the selection made - I know this is duplication of data, it's just the way the 'powers that be' wanted it. I'm changing it in my next version of the database!)
The two fields that are populated as a result of the selection in the combobox are migrating into the related table fine, but the actual selection in the combobox isn't - it's showing up as blank in each new record in the table.
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
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).
Can anyone tell me how I can query a number field for odd and even number. I have table with a field that contain only number which I would like to query out all the odd and all the even number to be able to print on a report.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I use a query to return values between two dates, here's the code I use:
Between [Select Start Date:] And [Select End Date:]
However because my dates are time stamped (they need to be!) the query omits anything on the end date, for example:
Between [01/09/2007] And [05/09/2007] will return values for the 1st, 2nd, 3rd, 4th but not the 5th - because (I think I'm right in thinking this but I might not be!) it only returns values upto midnight on the 4th? so 05/09/2007 13.42pm won't show up because it's after 11.59 on the 4th.
I can't ask people to enter in an extra day because quite alot of people who use our database won't remember and it'll cause alot of problems when they forget and get the wrong figures.
I've tried adding the following: " & "11:59:59""
onto the code but Access says the expression is typed wrong or is too complicated to be evaluated.
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
Has anyone ever encoutered a problem where when running a query to return a dataset which includes a memo field, the query only returns part of the info contained in the memo field?
Do queries limit the return of memo field data to 255 characters? I have run other queries on other memo fields in the past, and do not recall ever having this problem.
FYI, there is no indexing, criteria or joins/relationships on the memo field - whilst returning other fields, I purley wanna return whatever is contained in the field.
I am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
I'm quite new to Access and have inherited a database from a colleague. It is a database of references from which we have extracted pesticide active ingredient, pest species and crop species using forms. I am trying to create a query that will allow us to search and return unique active ingredient x pest x crop species combinations (but which will return all instances of this combination).
The complication is that the pest and crop species names are within the same field ('taxa'). They are distinguished by a label ('pest' or 'crop') in an associated field ('PestorPredator'), but a query using 'OR' will only return a crop OR pest, whilst a query using 'AND' will return no records (because something can't be a pest AND and crop).
I want to be able to create a table that shows the active ingredient, associated pest species AND the associated crop species each in separate columns. We have tried a couple of methods, including UNION and UNION ALL....
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
I have a continouus form that queries a table and displays various fields on the form. For each record in the master table displayed on the continous form I also need to show the result of a calculation in an unbound field.
The calculation needs to be based on the Count of the rows of a particular type in another table. This other table will have many different types of record - and I have a query that returns the count. This query is based on a SQL WHERE clause that is fed in a value from a field in the master table. To be concret a field in the master table has the name ID_component. This field has to be then used in the query to determine how many rows in this other table has a type of ID_component. From what I can tell right right now is that the query which is triggered from a combo box in the form is not getting the ID_component value on a record by record basis.
My question therefore is whether using continous forms one can get an unbound field to do a query on another tabel when the query has to be fed in a parmeter from a field in the original tabel?
If this is not possible - any ideas on how I can achieve something similar to I can obtain a count of rows and feed it into a continous form
1. User need to look up item name, while in a subform. He pressed F5 2. A form showed up, with a ComboBox (which got its values from a query) 3. User chose an item name in the ComboBox, and then pressed the Close button 4. The item name got copied into the subform.
Problem is, for some items/records, step 4 doesn't happen :eek: When I investigated, turned out that for some records, the ComboBox returned null (instead of the selected record)
I have a data base,one of the field contain Data like "ZZZ-DEFS#UUH1234567".
There should always be 19 characters in this field including #.I want to design a query which can sort out entries less than and more than 19 characters, so that wrong entries can be corrected .
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
I have a linked table in my access accdb file to a view on a SQL Server DB.
One of the columns is a date column, where only the time value is important, so it's stored in the default format, like 12-30-1899 12:00 AM.
In SQL Server, I can query records that fall between a certain time frame. It works in Access when written as an ADO query (that's another story), and it also works when I convert the view to a local access table.
But when I query it as a linked server, it will return everything is greater than 12-29-1899 11:59 pm, but when I try to search any date/time ranges on 12-30-1899 nothing is returned. This seems to be a bug in Access.
The reason I no longer use ADO, is that the results would not print. For some reason, when I go to print preview, Access would evaluate the query I'm passing through to SQL Server and throw a syntax error. I miss ADP.
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
One lists all the stock symbols and company names = SYMBOL The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
