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

Not Picking Up Table Value

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

Queries :: IIF Statement Only Picking Up First Value From Table

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

Query Not Picking Up The <> Operator

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

Query Not Picking Up Current Record

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

Queries :: After Importing Data Query Not Picking Up Newly Added Records

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

Picking Orders For Invoices

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

Picking Biggest Default

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

Picking Of Single Data Out Of A String Of It..

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

Forms :: Picking Up Wrong Record

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

File Picking To Fill A Form With Filepath

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

Forms :: Form Not Picking Up Previous Entries

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

Forms :: Form After Close And Reopening Not Picking Up Fields

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

Forms :: Merging Attendance In School Report Card And Also Picking Highest Score

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

Forms :: Picking Up Sub Form Totals From A Main Form?

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

Input Date By Picking Date From Calender

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

Query Returns Results From One Table/query But Not From Other Table/query

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

Extracting Data From Linked Db2 Table Using Access Make Table Query

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

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

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

Queries :: Update Query (table To Table) Not Updating All Records

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

Queries :: Join Multiple Fields From One Table To Same Table In A Query

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

Can I Attach An Append Table Query To A Field In A Table

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

Changing The Name Of The Created Table In A Make-Table Query

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

Tables :: Transfer Data From Table Or Query To Second Table

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

Queries :: Make Table Query - Find Table Name

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

Make-Table Query With Relationship To New Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved