Tables :: Interval Or Range Of Values

Oct 2, 2014

In a table, i have a field called operating draft (ships). I would like to be able to put an interval of data in this field, for example between 3 meters and 6meters. Indeed, for few ships the draft is flexible.

What kind of data type should i select and how can I set up an interval data.

View Replies


ADVERTISEMENT

Count Instances Of Records At Each Hourly Interval Within A Selected Date Range

Sep 30, 2014

I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key').  I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)).  When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set.  What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.

View 3 Replies View Related

Sum Of Values If In Date Range

Mar 6, 2008

Hi,

I've graduated from counting records to summing values now!!

I've got the data set that is shown below



caseNo source Amount Vat Total Date Paid
1 ted £50 £7.50 £57.50 01/01/08



I've actually around 3000 records going back to 2004. What I need to do is make a report that will show the following.



Source Amount (financialYear) Vat(FY) Total(FY) Amount(thismonth) Vat..

ted £50,000 £10,000 £60,000 £5,000
Bob .....



I've created a function in the module that returns the financial year that the record is in called getFN() which works fine.

I use 2 text boxes to input the start and end date of the reports and use one of the text boxes as the input for getFN()

Basically, I can get a report that shows the values for each source in the financial year ok, and I can do the same for the values for the month selected but I can't join the two queries up in a single report.

I'm not sure if I need to have a blanket query that joins them. I'm a little stuck.

Any help would be really appreciated.

Steve

View 1 Replies View Related

Beginner Needs Help With Searching A Range Of Values

Aug 9, 2005

Hi,

Im a web designer by trade and know pc's fairly well, im trying to help a friend who is pc illiterate build a database.
It would help him out and be some practice for me (always wanted to have a bash at access but never had a reason to do so)

Im totally new to Access and im trying to do the following.

He is an estate agent and wants a database where he can enter his prospective clients looking for commercial properties to rent, one of their criteria will be the size range of the property they want. Then when he gets a new property on his books he wants to be able to search the client list by the size of the property and display the results of any clients that the properties size falls within their required size range (phew hope that makes sense)

In an ideal world hed like the results to include addresses so he can mail merge the results and send out letters to any clients who might be interested.

So, ive managed to create a table of all his client details but now im stuck on how the query should work, ive read the help files in access (which seem to be written in martian) and im just not sure how i should try and do this. I started to create a parameter query but ive gotten very confused with all the options.

I can see that i could put fields in my table of Min Size and Max size but im not sure how to design a query that searches a single value between these 2 fields

Ive had a look through this forum but cant see anything that covers this, im hoping someone might be able to point me in the right direction, either with some advice or a link to some online help that might help me understand what i need to do.

Thanks for taking the time to read this post, apologies if its in the wrong place or a totally noob question.

Any help would be very much appreciated

Many thanks
Dave

View 5 Replies View Related

Reports :: Expression To Count Values In A Range

Sep 3, 2013

I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.

I have attached a screen print of the report I am working on.

View 5 Replies View Related

General :: Sum Values From Date Range From Specific Record

Mar 11, 2013

I'm working on a loan database for my personal use and obviously I'm new to access

See attachment ...

View 5 Replies View Related

Get Some Values And The Inmediatly Previous Ones Based On A Date Whithin A Range...

Jan 28, 2008

Hi everybody!

First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.

So, here it is my question:

I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).

What I want to achieve is, given a range of dates, show the last two controls of every person, something like:
id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control.
As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.

Thanks in advance! :).

View 2 Replies View Related

5 Minutes Interval

Apr 20, 2006

I have a query that shows a list from GPS software (car fleet management), unfortunately the software is not capable of doing a decent reports thus I'm trying to use its data and do it myself.

The table structure is as follow:

Car, Date, Time, Position.

The time interval I have now is 1 minute (short time format), I don't need it so detailed, that's why I was thinking to limit this into 5 minutes interval.

Is it possible?

Thanks for any suggestions.

View 1 Replies View Related

Range Lookup In Two Tables

Feb 15, 2007

Hello,

I have two tables in my access database.

A. Lets call one table "Map" and Map table has the following fields:

1) ZipCode_From
2) ZipCode_To
3) Salesperson

ZipCode_From : ZipCode_To : Salesperson
00001 : 10000 : Smith
10001 : 19999 : Jones
20000 : 29999 : Johnson

B. Second table is called "SaleMap" and SalesMap table has the following fields:

1) ZipCode

ZipCode :
00252 :
12568 :
22563 :

C. I would like my end query to look like this:

ZipCode : Salesperson
00252 : Smith
12568 : Jones
22563 : Johnson


Any help would be much appreciated.

View 1 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

View 5 Replies View Related

Number Of Days In One Interval And Not In Another

Feb 8, 2007

How would I find if 2 dates fall between 2 other dates?

For example:

This would evaluate to True
StartDate 02/08/2007
EndDate 02/11/2007

StartTestDate 02/09/2007
EndTestDate 02/10/2007

This would also evaluate to true
StartDate 02/08/2007
EndDate 02/11/2007

StartTestDate 02/07/2007
EndTestDate 02/12/2007

This would evaluate to false
StartDate 02/08/2007
EndDate 02/09/2007

StartTestDate 02/10/2007
EndTestDate 02/11/2007

View 5 Replies View Related

Queries :: DateDiff Everyday In Interval Day To Day

Feb 20, 2014

Let's say I have a StartDate: 02/02/2014 10:00 and EndDate: 01/30/2014 15:00

Is it possible to have this result in a query?

View 12 Replies View Related

Queries :: Date Interval In Same Field

May 24, 2014

I need to show the interval between dates. The dates are all held in the same field.

A/CDate
52601-Mar-14
52601-Feb-14
52601-Jan-14

View 5 Replies View Related

How To Create Date Interval Using Combo Box

Jan 13, 2013

How to create a date interval using combo box?

For example, i have a receipt form consisting of receipt date from 1/1/2012 to 31/06/2012.

But I want to view the receipt form to just show the date from 1/3/2012 (can be changed) onwards or 1/4/2012(can be changed) onwards.

View 2 Replies View Related

Excel-export Date Interval Problem

Sep 2, 2006

Hi!

I'm currently facing a wierd problem, and I really hope you can help me!

I have a query called "fsRapportSalgsoverblik2" which contains a list of all sales made from a company. I want to export that list to excel, which is no problem at all... But when I try to set a date interval, so that only the sales made in a certain period is exported, I get errors. I've tried to implement the interval-limitation in two different ways:

1) Inserting "WHERE date BETWEEN "field A in form" AND "field B in form" in the query from which the data is exported (when using the data-view to view the query output i can see that it works perfectly).

2) Inserting "WHERE date BETWEEN "field A in form" AND "field B in form" in the OnClick-code of the "export to Excel" button, where the export-code is written. So instead of saying:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM fsRapportSalgsoverblik2;")
I change it to:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM fsRapportSalgsoverblik2 WHERE date BETWEEN "field A in form" AND "field B in form";")

But no matter which solution I choose, I get the error: "Too few parametres. At least two was expected..." (free translation from danish version of Access). I really don't get it. When exporting to Excel without a date-interval, it works perfectly. But when inserting a date interval, it doesn't work, no matter how I do it....

Please help!!

// JR

View 3 Replies View Related

General :: Generate Numbers With Fixed Interval

Sep 9, 2014

I have a range say 12.03 to 13.11.

I need to generate all numbers falling within this range with 0.01 increment.

(This increment is based on the numbers; if the numbers have 2 decimals, it should be 0.01. If the numbers have 3 decimals, the increment should be 0.001. Both the numbers will have the same number of decimals)

The answer is: 12.03, 12.04, 12.05, etc. etc. till 13.10, 13.11.

Is there a way we can do it in MS Access ?

View 14 Replies View Related

Tables :: Count Unique Values With Different Values

Feb 7, 2014

i have one table in which ID is Primary ID with Different Values

Like

ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U

it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.

View 8 Replies View Related

DateTime Period Between Last Record In Table And Given Time Interval

Jul 5, 2005

HI all,
I am still new in Access databases :o

I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().

When this is done I have create calculations with extracted records using agregate functions.

I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.

Please help! :confused:

View 7 Replies View Related

Tables :: Form That List Available Static IPs In Network Range Blocks

May 29, 2013

I've been tasked with coming up with a table structure that will allow me to make a form that lists the available static ip's in my various network range blocks (we have multiple off-site locations and use different ip ranges at each site).

I have the vision in my head of the form, you would select the block range from a drop down list, then see all the available addresses in that range. From there you could assign mark one as used and it is no longer listed on the form (I'm thinking a simple yes/no checkbox). What my co-workers would love to see is if there were entering a new PC assignment and put one of those available IP addresses into that forms record (different table too) then the ip would get deleted from the available pool as well.

View 2 Replies View Related

Queries :: Breaking Out (Timestamp And Total Duration) Into 15 Minute Interval?

Dec 20, 2013

I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?

Raw Data
EmployeeActivityTimeStampStartTimeStampEndTotalDuration
Doe,JohnSomeActivity12/16/13 9:06:02 AM12/16/13 9:57:14 AM0:51:12
Smith,JaneOtherActivity12/16/13 9:22:15 AM12/16/13 10:06:55 AM0:44:40

Query to break out the total duration time in to the 15 minute interval it fell into

EmployeeActivityIntervalIntervalDuration
Doe,JohnSomeActivity12/16/13 9:00:00 AM0:08:58
Doe,JohnSomeActivity12/16/13 9:15:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:30:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:45:00 AM0:12:14
Smith,JaneOtherActivity12/16/13 9:15:00 AM0:07:45
Smith,JaneOtherActivity12/16/13 9:30:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 9:45:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 10:00:00 AM0:06:55

View 2 Replies View Related

Generate A Field In Query With Hourly Incremented Data Between MIN MAX Interval

Aug 12, 2015

How can I put a field in my query, with all data hourly incremented between 01 January 2015 to 31 December 2015.

View 5 Replies View Related

Develop A Query In MS Access 2010 To Join Two Tables Using Three Joins One Of Which Is A Date Range

Apr 15, 2015

I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.

ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART

[code]...

View 4 Replies View Related

Automatic Recurring Entry Based On Start Date And Nominal Interval Period

Feb 15, 2012

How to create a table that has a recurring entry, based on a start date and a nominal interval period of e.g. one calendar month?

Is it possible to have these entries applied automatically each time so that they appear, as required, when the table is opened?

For example, for the entry of regular payments due per month.

View 3 Replies View Related

Tables :: Passing Field Values Between Tables

Dec 10, 2014

Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.

Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.

The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.

In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.

View 7 Replies View Related

A Query To SELECT A Columen Within An Interval Which Conatains TEXT+DIGIT+TEXT

Dec 28, 2006

I have a column which contains "text digit text" as "AAA 222 BBB". The numbers of letters or digits can vary.

I need to SELECT the column which contains digits in a specific interval. For Example I have
"DFS 673 JKK"
"A 3454 LJLJ"
"SD 854 JKLJD"

I need to SELECT the column which contains 600 < Digit < 700 the result of the query in this case would be "DFS 673 JKK" because 673 is between 600 and 700.

Thanx for any clue or suggestion...

View 1 Replies View Related

QRY To Get Values From 2 Tables

Sep 26, 2006

Hi all....

I have been beating my head for 2 days on this. Any help will be GREAT!
I have 2 tables: CLIENTS and RELATION

NAMES has the client_ID, first_name and last_name along with other information for clients. RELATION is how the clients fit with each other. RELATION has relation_ID, Relations_description and up to three fields where we can enter the ID of clients from the client table called Rel1_ID, Rel2_ID, and Red3_ID. Now all the three rel_IDs come from CLIENTS and stores only the client_ID. There are multiple relationships.

How can I display the names and not the IDs for the clients?

I am able to display only one client name. I need this for form as well as report. I can do dlookup for form but don't know for reports. I am thinking that if I have it in the query, then I can use that for the form as well as reports.

I am able to show the the name for one client by running the following qry. But I am not able to modify that to show the other 2 names.

SELECT RELATION.Team_Name, RELATION.relations_Description, RELATION.rel1_ID, RELATION.rel2_ID, RELATION.rel3_ID], [client.f_name] & " " & [Client.l_name] AS Client_Name
FROM RELATION, CLIENTS
WHERE ((([relation.rel1_id])=[client.client_id]));

View 3 Replies View Related







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