I have a six table database and have a query to show all records,(at the moment) but although i have put 3 records in for testing it only shows the first record. Any help gratefully recieved i'm pulling hair out.:mad:
View Replieswe have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...
now for the query:
i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design:
Amt: [amount]&Space(1)&[units]
this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...
i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work... :mad:
Hi Lads
Hi guys, I want to return all the records which contain information under a particular column heading, hence the statement thats supposed to return all tables where Sector colum is not empty. I am new to SQL and Access so not sure why this simple query isn't working. It is a subform and displays the results in the "details" section of the form below the controls.
I think it could be something to do with the fact that its a subform, but Im not sure about this.
Me.RecordSource = "SELECT * FROM [tblRecordInfo] WHERE Sector <> "" "
any ideas guys? any assistance is greatly appreciated
thanks in advance
I have a table filled with computer monitor information (tblMonitorManagement). I had query that uses combo boxes (cboSerialNo, cboRoom, cboDepartment) on a form for search parameters. The query is called qryMonitorManagement_Sub. The results are displayed in a subform (frmMonitorManagement_Sub). This works for both my desktop and also my laptop based forms/queries/tables.
Although this form works when using cboRoom and cboDepartment, it does not work when using cboSerialNo, so I decided to simplify it down to get to the root of the problem...here's what I did.
I created a new form, created a combo box (again called cboSerialNo), and had it look up the serial numbers in tblMonitors. Simple.
I created a query called qryMonitors_Test, added all fields from tblMonitors (not using the * option). I then set the SerialNo field criteria to the contents of the cboSerialNo on the new form. Simple. For now I'm ignoring searching on anything else.
I ran the query with no search parameters before I added the criteria, and it obviously displayed all the records in tblMonitors. Fine.
After adding the criteria (using Build...to prevent typos), I used the form to select a serial number, run the query and get no results returned.
If I copy and paste the same serial number and change the SerialNo criteria in the query to search for that it works fine. As soon as I tell it to search for the combo box contents it returns no data.
This is driving me completely bat-poo. It's nothing I've not done a squillion time before but it just refuses to work. I've got to assume it's a query issue as opposed to a form issue, but things don't get much simpler than this should be!
I have a query that filters two tables. 1) Status 2) Name
The status is 1,2,3,4,5 from an option group on my form.
In the query I have <>3 for the status, whereby I only want records that are status 1,2,3,& 5.
When I run the query with all names it works perfectly, however I am trying to run the query for only a select few names versus all of them.
The problem is I do not know how to select only a few names versus all of the names and make it work. When I try to add names i.e "Doe, John" my Status pulls all records including option 3.
Can anyone help? I do not even know where to start seaching.
Thanks.
Fen How
Hi Guys,
I've tried searching the forums for an answer with no joy.
I'm trying to create a very simple DELETE query, which I've never had an issue with before.
Basically I have two tables
Dbo_PDRPerson (PersonID, RoleID, PDRID)
tbl_InfoByPDR (PDRID)
Basically I want to delete all records in Dbo_PDRPerson where a corresponding PDRID record exists in tbl_InfoByPDR
My current method:
DELETE dbo_PDRPerson.PersonID, dbo_PDRPerson.RoleID, dbo_PDRPerson.PDRID
FROM dbo_PDRPerson INNER JOIN tbl_InfoByPDR ON dbo_PDRPerson.PDRID = tbl_InfoByPDR.PDRID;
This tells me to "Specify the table containing the records you want to delete"
Any ideas
Many thanks
i have an sql statement:
aa = "hello"
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
Hi
Using a toggle box to ask the following question, i have data that contains either OPEN or CLOSED against each record. I tried using the following in the criteria for the specific data to either select all the OPEN records when the box is clicked and i would like to select all records OPEN and CLOSED when it is nto clicked
IIf([Forms]![MEQN_ProdACStn_Picker]![Toggle38]=True,"OPEN","")
This didn't work and a number of other attempts failed as well...
How do i do it in the criteria selection or is it not possible
Cheers
Newbie Jasper
I've done this tons of times, so I don't understand what's happening... The title says it all: when I update the ID of one record of the main table, the referenced records on related tables won't update even though they have a relationship between them with the option "Cascade update" clicked.
In the attached database, the main table "Expedientes" is related to table "Actuaciones".
I have witten a code in the close event of a "Primary" form that would update a Combo boxe on a "secondary" form only if the "secondary" form is open.
ie:
Private Sub Form_Close()
If Forms!frmEnquiry.Open Then
Forms!frmEnquiry!CboCustomer.Requery
Else
DoCmd.Close
End If
End Sub
This code keeps giving me an error, is anybody has got any idea why?
Thanks in advance.
Hi there,
I have a really simple question.
I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
Does this make sense?
Thanks,
Row
Hi guys, wondering if you could help me, I can't seem to get this right..
I have two tables in question:
Items: StyleTypes:
------ ------------
ItemID ItemType
ItemType StyleType
Style
Lots of Attributes
ItemType is a list of items (Table, Desk, Bookshelf, Chair)
Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
Hi all,
I have very little knowledge of databases and even less about writing queries.
The problem:
I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns.
I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
Many thanks,
Carl
hi
have just designed a rather simple music database for my dad to use at home as he has large collection.
have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query
alan
I have a query that returns the uncompleted jobs from a single table ( its a calls database)
all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls
Thanks in advance to anyone who helps.
SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).
I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:
SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed
FROM informix_shrink_head, pcw_loc_master
WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null))
ORDER BY informix_shrink_head.business_unit;
However, I get the same store multiple times and for multiple post dates.
I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.
Thanks,
A Gator
Hi guys. I have been away a while and boy am I rusty.
I would like to show how many available licenses there are. Here is what I have:
Software (Office 2003)
license purchased (8)
license used (2)
I added an expression along the lines of:
Available: Sum([tblSoftware]![Number of licences purchased]-[tblPC-LicenceRelation]![Number of licences])
This gave 14 and not the desired result of 6!!!
Simple I am sure!! :o
Phil.
Hi Guys,
I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.
I understand that I will need to create a query to display just the last record and then email it.
So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.
Please be gental with me im still quite new to all this.
How do i create a query that just displays the last record?:confused:
Thanks in advance.
Rich
I built an update query that adds two strings of text together with a _ inbetween.
eg
field1 = Hello
Field2 = Jamie
after update= Hello_Jamie
This works fine but when i attempt to run the code in SQL on an after update instance i can't get it to compile
The SQL straight from the working Update query is:
UPDATE tblPID SET tblPID.PIDPIDNoRev = (tblPID.PIDno)+"_"+(tblPID.PIDRev);
I have tried quite a few different ways and believe it to be a probelm with the speech marks or underscore
DoCmd.RunSQL "UPDATE [tblPID] SET [tblPID].PIDPIDNoRev = (([tblPID].PIDno)+"_"+([tblPID].PIDRev));"
anyone got any ideas? i'm sure it's pretty simple
Thanks, Jamie
HI,
I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.
Regards,
Elio
Hi,
I think i am being a bit thick, all I want to do is create a report of projects without products....
I have a table of Projects (customer name, details etc...)
I have a table of Products (product name, config, details etc....)
Every project will eventually have one or more products allocated to it...
How can i query the database for:
'select all projects created without any assigned products... yet'
I'm having a post xmas block!!!
Thanks
Hi there..
I need a simple query...
I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?
Hi,
I am trying to run a query to retrieve all rows in all tables using a row called Project Status. I am however not able to retrieve any data what so ever with the wizard and by using the limited sql I know. I have attached a copy of my tables if it helps.
The relationships are as follows: Release to PCR - One to many, Sponsors to PCR - One to many. The column i want to filter on is Project_status located in the PCR table.
Does anyone have any ideas how i can do this plz?
Thanks in advance for any help!
Ket
I wish to create a very simple query but cannot find the correct syntax mentioned in any documentation.
The WHERE clause needs to have the effect of ignoring certain records in a table ie
WHERE fieldvalue <> 12 AND fieldvalue <> 23 AND fieldvalue <> 37 etc etc for up to an unspecified number of values.
Is the above the correct syntax (very tedious to construct in expression builder or natively) or is there something more simple available such as
WHERE fieldvalue <> (12, 23, 37 ......)
Many thanks
The following query never returns, even though there is an index on the name field in the Patient table:
SELECT p.Name FROM sheet4 AS s INNER JOIN Patient AS p ON p.NAME LIKE s.LASTNAME + ', ' + s.FIRSTNAME + ' %'
Hi
I am re-visiting access for the 1st time in a couple of years to develop a quotations system and I am just messing around at the moment with Northwind trying to work out how it all works.
I have come across a problem that is baffling me....
When displaying the order details table (referenced from products), the product ID is disoplayed in text form, not as a number. Whenever I try and do this with 2 similar tables using the query builder I always get just the other tables number. I have no idea what I'm doing wrong as every field, attribute, join etc seems to be just the same as the sample tables.
Please could someone help a duffer? :)