Storing Multiple Ranges Per Record...thoughts?

Jun 21, 2007

Hi, I'm very new to this so your patience and help is requested.

I need to store multiple ranges per record in my relational db, i.e.

A: 3500-4800, 5230 (spot) , 6400-7900

Etc, I need the data to be searchable with minimal fuss and coding.

Any help would be very very welcome.

Cheers

View Replies


ADVERTISEMENT

Querying Multiple Date Ranges

Sep 26, 2007

I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.

Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...

View 2 Replies View Related

Forms :: Storing Multiple Values

Apr 29, 2014

In my database (attached), I need to be able to store multiple notes for tasks performed in various cities -- each note has a small description (more on that at the bottom of this post). For the "San Antonio, TX" task, notes "Heartbleed" and "BAT Scan" apply. From what I understand, this is a many-to-many relationship which will require the use of a junction box. My structure is like this:

tbTasks
TaskID (PK - autonumber)
Task

tbNotes
NotesID (PK - autonumber)
Notes
Details

jcttbTaskNotes
TaskID (FK from tbTasks)
NotesID ( FK from tbNotes)

My mainform (fmTasks) is based off the tbTasks table; my subform (subfmTaskNotes -- continous form) is based off the jcttbTaskNotes junction table. The Master/Child link for the subform control is TaskID. In the subform I have a combo box bound to the NotesID field in the junction table that allows me to choose a note.

My problem: I also have a "Details" text box in the subform that I want to be populated with the description of the Note selected in the combobox mentioned above. For example: if I choose the "Heartbleed" note, then I want that "Details" field to be populated with "Mitigate vulnerability associated with CVE-2014-0160". I assume it has to be somehow tied to the "Details" field of the tbNotes table, but how to implement that. How do I get the Details text box to display the details for each note?

View 1 Replies View Related

Storing Multiple Dates In A Single Field

Feb 14, 2012

I am facing an issue with an access table.I have a field named "Contact Dates" , supposedly that field will store in a drop down list all the dates that the contact with the related company was made, how can I store it ? I am using in the current moment a memo data type to store the data and a comma for splitting them. How to create a drop down list for storing and displaying all the recorded dates and recording all the new data values as date format.

View 3 Replies View Related

Storing The Date The Record Was Entered Into A Table

Jun 27, 2006

Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.

I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.

Thank you very much if anyone could point me in the right direction.

View 3 Replies View Related

Record Not Retaining/storing Combo Box Selection

Mar 9, 2006

hi,

i have a form that uses combo boxes for entry, some of the combo box entries are dependent on preceding combo box selections.

i have managed to make the combo boxes present options based on the previous selections, but am finding that after i move on to another record, some of the combo box entries are not being retained or stored in the record.

It happens only on some records and sometimes when I return to it again the selections are there even though on a previous ocassion they were not.

Can anyone suggest what may be causing this?

Regards

K

View 5 Replies View Related

Some Thoughts

Jun 23, 2005

Hi All,

I am currently using some of the code from ghudson's browsing2.mdb for importing XLS files into a database that I making. After pressing the import button which triggers the importing of the file it imports the contents of the XLS into a table called import and into another table at the same time. Both the import table and the other table have the same structure. For the sake of argument lets call the other table "table B".

I want to know of a way of how during the import of the XLS it can flag with a message box to say that that the information already exists in table B, and exit out of it. I was thinking this would be done in VBA. I have written some part of it (very basic) and would appreciate someone's opinion if I am on the right track. It basically searches if the same data lies in the 2 tables and pops up a message box. I would welcome any other solutions, but I would prefer it to be VBA.

----Start of Code---------------------------------------------
Dim strCompare1, strCompare2, strAppend1 As String

strCompare1 = "Select table B.field A from table B where (select import.field A from import);"

strCompare1 = "select import.field A from import where (select table B.field A from table B);"

CurrentDb.Execute strCompare1
CurrentDb.Execute strCompare2

If strCompare1 <> strCompare2 Then
strAppend1 = "INSERT INTO table B ( field A, field B, field C) SELECT
Import.field A, Import.field B, Import.field C FROM Import;"
CurrentDb.Execute strAppend1
Else
MsgBox "Piece-Marks already exist in the database, so if it is an update
then use the update function"
End If

--------End of Code--------------------------------------------------

Cheers
Greg

View 1 Replies View Related

Thoughts On VB.Net Front End

Nov 21, 2007

I have a wonderful database I designed almost 2 years ago. Other than a few hiccups with corrupted data (http://www.access-programmers.co.uk/forums/showthread.php?t=135483), my employer is 100% satisfied with it.

I’m graduating this winter with an AS in computer programming and I would like to update the database to a VB/ADO .NET (VS 2003) front end before I leave. Being a VB developer, I have had my fair share of “quick fixes” when I could not work out a solution with VBA. VBA is the evil twin of VB lol.

Other than PHP w/ MySQL I have never used two separate entities for data management. It’s always been strictly MS Access as the database and front end.

So my question is what major differences should I expect that I took for granted with MS Access’s built in functionality? Such as subforms with master/child properties, navigation, printing reports etc. Will I have to write code for every little detail that MS Access handled for me?

What are your thoughts on using VB.NET as a front end? Did the pros out weigh any cons from your experience with this setup? Any thoughts on using Crystal Reports?

View 3 Replies View Related

Help With Thoughts/idea's On Doing This

Oct 14, 2004

Looking for some advise and help. I think an array will be recommended and if so, please help with code on how to do this.

I am trying to track prescription (Rx) dates (RxOutDate) going out to medical professionals on a particular order. There can be mulitple Rx going out so there will be multiple dates of when those Rx went out. What I would like to do is have the earlies date posted. ie one Rx went out on 10/13/04 and another goes out on 10/14/04. I would like for the 10/13/04 date to show. I'm using a continuous form to display all Rx's per order, on frmRx. I'm trying to display the date out on the form containing the order (frmOrder).

Any thoughts would be greatly appreciated. If an array is suggested. Please help with code on how to it. I've never written one so I would definitely be in the dark.

Thanks ahead of time,
Shane

View 1 Replies View Related

DateDiff Issue.. Any Thoughts

Feb 27, 2007

I am trying to calculate the number of weekdays between two dates in an access query.

I'm using Expr 1: DateDiff("w", #4/01/2006#, #02/26/2007#)

When I use this expression with "d" instead of "w" I get 331 days, but when I use "w" I get 47 days. I know this can't be right.... Is there something I'm missing here?

I looked up on msdn and "w" is for weekday. Is there something that I need to do to be more specific?

Any help is appreciated.


Thanks

View 4 Replies View Related

My Questionaire Table Design-normalization! Your Thoughts Please?

Jun 16, 2006

Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest.

Meaning, a user will need to fill out the same form twice, one for himself and one for his guest.

Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to?

Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?

View 3 Replies View Related

Modules & VBA :: Storing A Single Record In A Single Dimension Array?

Sep 8, 2014

Any Single line of code available?

View 2 Replies View Related

Sequential Ranges

Aug 19, 2005

I need a form to set up records

The table is something like

AutoID, Sequence , Low, High

Data would be

1, 1, 1 499
2,2,500,999
3,3,1000,9999
4,4,10000,999999

etc

Ho do I go about ensuring my sequences are correct

ie I wouldn't be able to add a record - 5,5,100,200

or if a user was to delete a record the user would be informed the sequences were out?

Thanks for any assistance.

View 2 Replies View Related

Postcode Ranges Question

Jul 27, 2005

Hi all,

I am relatively new to access and need some help.

I have table with ranges of postcodes (NL) and regions. For instance

Table_Regions_Range:
Amsterdam 1000-1119
Haarlem 2000-2099
etc

And i have a records of schools:

Table_Schools:
SchoolA 1050
SchoolB 2050
etc

I want to query the data base and lookup in wich regio a school is based.

Where do I begin?

Thanx in advanced

Jan

View 1 Replies View Related

True Date Ranges

Nov 6, 2006

I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!


Between Date()-Weekday(Date()-1) And Date()-1

View 2 Replies View Related

Counting By Date Ranges

May 19, 2005

I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.

This is my current sql view.

PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track"))
GROUP BY tblContent.Title, tblContent.[Date Assigned]
HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#)); 


Thank you in advance for any help you can offer.

View 6 Replies View Related

Test Score Ranges

Feb 13, 2008

Hi all

I have a bunch of student test scores. The test is scored out of 100. I want to COUNT the scores (ie I want to know how many kids scored 50 etc...). This is easy enough except the crosstab query will be very wide ie 100 columns.

I cant seem to reduce the size by grouping them into ranges (ie count the number of kids that scored between 50 and 60 in a cross tab. I've tried variations with select queries but without luck.

Maybe there is a way to code it.. and then show it on a report??

Any ideas?? Thanks

View 7 Replies View Related

How To Compare Two Date Ranges

Feb 18, 2013

I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.

I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.

View 1 Replies View Related

Weird Table "corruption" Occurring. Thoughts?

Mar 10, 2008

I put a split database (created in 2000 format using Access 2003 SP2) on the network three weeks ago. Three times the entries in two of the columns have been changed for every record, essentially making the data useless. I have not been able to narrow it down to a specific event that is causing it in order to program around it. Any thoughts would be greatly appreciated. Here are the details:

The table involved is called tblTitleLog. It contains 13 columns, the first of which is an autonumbered ID field. Column 7 is called CompletedCSA. Column 9 is called CompletedDate. The data in those cells could be different, from record to record, or could be the same as several records on either side of it. The data in those two cells can be updated either of two ways:


On a per-record basis using a form that contains controls tied directly to the cells for a given record, or
On a form that incorporates a multiselect listbox and an Update button. This form is specifically designed to allow batch updating of those two columns.


On three separate occasions, the data in those two cells suddenly changed for all 6940+ records. For example:

This:
CompletedCSA CompletedDate
Timmy 2/2/2008
Bobby 2/5/2008
Joey 2/10/2008

Would suddenly change to:
CompletedCSA CompletedDate
Bobby 2/5/2008
Bobby 2/5/2008
Bobby 2/5/2008

No warning messages about multiple people trying to change the same record(s) is displayed. None of the users have been able to spot a trend in something they were doing when it occurred. The code for the Update button on the batch update form only changes the records with the ID of the SelectedItems.

This is the code from the Update click event on the BatchUpdate form:

For Each i In Me.listboxTitles.ItemsSelected
strWhere = strWhere & Me.listboxTitles.ItemData(i) & ", "
Next

If Len(strWhere) > 0 Then
strWhere = "WHERE ID IN (" & Left$(strWhere, Len(strWhere) - 2) & ")"
End If

strSQL = "UPDATE tblTitleLog " & _
"SET tblTitleLog.CompletedCSA = '" & Me.cboCompletedCSA.Value & "', tblTitleLog.CompletedDate = '" & Me.txtCompletedDate.Value & "' " & strWhere

CurrentDb.Execute strSQL, dbFailOnError

The controls on this form are all unbound.

Duplicating the same actions the users reported just before it happened didn't seem to make a difference. So far, I haven't been able to duplicate the conditions. Management is not happy and I'm frustrated. Any thoughts or suggestions on how to prevent this from happening again?

Thanks,

DiverGuy

View 10 Replies View Related

Date Ranges In Reports Or Queries???

Aug 23, 2005

Hello,

Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.

Hello all, this is probably simple but im new to all this!

I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?

Thanks

View 2 Replies View Related

Parameter Query With Date Ranges

Dec 15, 2005

I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?

I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.

Thanks in advance!

View 2 Replies View Related

Newbie Question About Date Ranges

Jan 12, 2007

I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.

In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;

>=[From] <=[To]

This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?

All help appreciated.

View 5 Replies View Related

Calculating Dates Between To Date Ranges

Dec 27, 2007

Calculating dates between TWO date ranges:

I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.

Example:

test table:

Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith


Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?

(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)


Don

View 3 Replies View Related

Form Issue With Ranges From A Table. Help Please?

Jan 4, 2006

I have a form that is similar to a post card. The fields are linked to a master table that contains all the customer data such as dates of service, street address, PO Box, City, State and zip. What I need to do is have a method to pull into the post card form a range of dates, so the form would have only January customers in it. I have parameter query that pulls ranges but it comes up in table form. I need the range to come into the fields on the post card so I can then print just the month of January.

Do I need to create a button on the form for the ranges and if so how do I get it to pull just one month's customer data into the fields on the post card form or can this be done?

Thanks in advance for the help.

View 2 Replies View Related

Queries :: Applying Calculations To Date Ranges

Aug 10, 2015

I need a query that will relate values to given date ranges. For Example:

Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom

Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15

Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:

Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)

Not sure how to set this up the right way.

View 3 Replies View Related

Modules & VBA :: Import From Excel (variable Ranges)

Jun 12, 2014

I am trying to import data from an Excel file to MS Access (2013).

The Excel sheet consists of 700 columns. A group of 7 columns (always same header) are to be implement in Access one among the other.

That means:

At first columns A-G, than columns H-N, than columns O-U

I am trying to solve this with a loop (as you can see in the code)

But, if I start the modul I get this error:

"Runtime error 1004. The method Worksheets for _Global object failed"

But the module works, of I start it a second time. The error message appears only at the first start.

Apart from that, the code works. Sometimes, the module imported empty rows into the Access table.

Code:
Sub ExcelImport()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Open FileName:="C:Users...DesktopBeispiel.xlsx"

[Code] ......

View 1 Replies View Related







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