Query Only Picking Up 1 Didgit From Table
Mar 15, 2007
Hi
I'm a bit new to SQL, and have what is probably a rather simple question:
I have a query, which i want to pick up a figure from a table.
SELECT *
FROM Feb_Closed
WHERE Time_to_Close>'6' AND Team_Owner='Comp& Ben';
This give me a load of rows with Time to close between 6 and 9 but nothing else. The problem it is not counting everything over 9 ie i think that it is just looking at 1 digit in the table and so ignoring 10, 11, 12, 13... etc etc.
Any help gratefully received.
Thanks
View Replies
ADVERTISEMENT
Dec 10, 2007
Hi everybody,
I have the following table:
ID ThicknessFieldRange1 ThicknessFieldRange2 Factor
16 0.0641 0.068 0.97
17 0.0681 0.072 0.98
18 0.0721 0.076 0.99
Now, I have a form that has a 'Thicknesstxtfield' that when calculated in the form its value is 0.072 in this case . Based on the range this value falls into, I want to get the correspondant 'Factor' which in this case would be '0.98'.
Guess what, ACCESS is telling me that it cannot find the value '0.072' in the table and gives me an error. Any ideas on this?
Notice that all the fields except "ID" are 'Doubles' with 4 decimal places, and I find the values using the 'DLookUp' function. I know that's where the error is because all the other ranges work fine.
Thanks everyone
View 4 Replies
View Related
Jun 10, 2013
I have a table of rates based on ages and I am doing a query that will calculate your rate based on your age. I am using the IIf statement like this:
EERate:IIF([EEAge]>25,.345,IIF([EEAGE]>30,.425,.3)
The query will only use the first rate even if age is above 30.
View 3 Replies
View Related
Jul 3, 2006
I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)
I want a query to return all the results in the first table that are not equal to the second table
I have tried the following but it returns all 54 records
Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));
I have tried changing the where clause to have it as
Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);
but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records
View 1 Replies
View Related
Sep 9, 2005
Hello,
Can anybody see what I'm doing wrong here. I have the following query
SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName
FROM Contacts
WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK)
ORDER BY Contacts.ContactNr;
The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.
View 3 Replies
View Related
Apr 21, 2013
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
View 7 Replies
View Related
Oct 31, 2006
Hi everyone,
I'm sort of new to Access, but over the past few months I've been able to develop an inventory database for my work which turned out quite nicely and does everything I need it to, except for one thing.
I want to also create invoices for my customers, and after looking at examples I understand that most people link the "order" with the invoice, so that there is one invoice per order. However, I need the ability to combine many orders into one invoice. That's where I'm stuck.
I don't know how to arrange my relationships and forms so that I can "pick" the orders I want on my invoice, and not at the time of the Order, but at some point after. Basically my orders will be established, but their allocation to invoices won't, until I come along and put them into their rightful bill.
Does anyone have any thoughts on this? I'm pretty stumped.
Thanks a lot for your help.
View 2 Replies
View Related
Mar 21, 2006
I have a table with ID1, ID2, and AMT.
IF ID2 is NULL, I want to update it with the non-null ID2 where the ID1 matches and has the largest sum of AMT.
For instance
ID1 ID2 SUM(AMT)
1 333 1000
1 444 3000
1 <null> 5000
In this case, the third ID2 would get the value of 444 since it had the highest amt.
I'm guessing this involves sub-queries, and I have a real blind spot where they are concerned.
Thanks,
David
View 3 Replies
View Related
Jul 31, 2007
hi,
I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....
for an example..
1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW
2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159
See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...
Can someone please advice me on how i can go about doing this?
View 2 Replies
View Related
Dec 6, 2013
I have a table called "Cities" which has only two fields; City & Province.I have connected it in a relationship with another table as "Include ALL records from 'Cities' and only those records from 'tblInstallations' where the joined fields are equal."I have put an "AfterUpdate" event which works and everything.
It is working fine and updates the province correctly.The problem is that is putting another city from same province in the "City" field even though I picked up different name.
View 6 Replies
View Related
Jan 18, 2006
I'm trying to get a file picking window to open in order to fill a form in access with the files path...and honestly...I have no idea how!
Any advice?
Thanks!
PS: I work on Office XP
View 1 Replies
View Related
Sep 9, 2013
I'm fairly new to databases and have been attempting to build a multi-table database. It's not properly rationalised, but I wanted to test run it to ensure it was fit for purpose first.I've built my form, which is pushing data out to several tables, but I cannot seem to get the form to pick up previous records.
View 3 Replies
View Related
Sep 11, 2013
I have cleared the data out of the database and started testing it to check its usability.I entered in a full record, and flicked back and forth between the records, and everything was looking good.
Then I closed the form, and re-opened it and my record was no longer populating the form fields.I've literally changed nothing bar one field that was changed from number to text.
View 5 Replies
View Related
Feb 7, 2014
I want to have a report card where I can have attendance embedded in for each students and also the highest score for each class each session.
View 4 Replies
View Related
Mar 22, 2013
I have converted an old Access 97 database to Access 2010. Mostly it works fine but I have a major issue with the invoicing forms. It was working in the old database but I cannot get it to work in this version.
I have the usual invoicing option where the lines of the invoice are displayed in a sub form for that customer and line totals calculated. This works fine. I have a sub form total text box in the footer of the sub form which I want to pick up from the main form so that I can add the delivery charge and VAT.
View 6 Replies
View Related
Jun 14, 2005
I am not a professional programer but would like to do a simple database for my group to track research progress. Is that possible to "pick up a date from drop down calendar" then the date will input to the cell (save in table) in a form, instead of typing it in? I saw that in lots of web sites, but can not figure it out how to do it in Access. Tried "Canendar Contol" and "LANDesk Data control" in the toolbox.
Thank in advance
PK
View 4 Replies
View Related
Mar 2, 2007
Advanced apologies for the long question.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
Can anyone figure out what I'm doing wrong?
Many thanks in advance
View 3 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Jan 26, 2006
Hi,
I have a field in one table that needs the sum from fields in another table.
How do I go about doing that?
Thanks,
Trena
View 1 Replies
View Related
Sep 23, 2007
Dear Access Expert
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
View 6 Replies
View Related
Apr 25, 2014
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
View 3 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
May 31, 2005
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
Thank You
View 1 Replies
View Related
