Queries :: Check If Period Breakdown Is Complete
Aug 31, 2014
I have 2 tables:
-tblServices
-tblPricesPeriods
Each of the tables has fields for:
-StartDate
-EndDate
The logic behind the database is that each service is contracted for certain period, from StartDate until EndDate (if EndDate is not given - null - it means it is a permanent contract). For each service, we need to input prices. As prices may change during time, we may have different prices records for a service - we need to have the service period broken down into subperiods with different prices. So we need StartDate and EndDate for each period for which particular prices are valid (again, if EndDate is not given - null - it means the prices are valid "forever" into the future from the StartDate).
My problem is: I want to have a check that would return the number of services which period (tblServices) is not fully reflected in the prices periods (tblPricesPeriods). Those would be for example:
-gaps between subperiods (subperiod1 finishing 30.09.2014 and subperiod 2 starting 3.10.2014)
-service period (tblServices) limited by EndDate (not null), while prices period for this service (tblPricesPeriods) having null as EndDate
-start date for service (tblServices) earlier than earliest start date for prices periods for this service (tblPricesPeriods)
-...
Preferably, I'd like to use a normal query for that (or set of queries), but I'm opened to other options.
View Replies
ADVERTISEMENT
Oct 12, 2004
Im tinkering with a new form for data input..
We use 2 different forms for employees based on status. Pending, and active. Staff members should not be able to be set to active unless all the fields on the Pending form are completed. Is there anyway I can check to make sure the form is completely filled in before allowing a status change to Active?
View 5 Replies
View Related
Apr 25, 2005
Hi
I have sendkey "{f7}" on loss focus.
this works great as a spell check but then I get the mesage box that spell check is complete.
How do I stop this box from occuring?
View 2 Replies
View Related
Jan 28, 2015
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
View 9 Replies
View Related
Dec 3, 2012
I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.
Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:
PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")
i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December.they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)
View 3 Replies
View Related
Sep 28, 2014
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
View 4 Replies
View Related
Jun 16, 2015
Query that would flag me if a sum over any 7 day period reached a certain amount.
Setur : I have a query of SAMPLEs. Each sample can have a PERSON associated with it. The samples have an AMOUNT also associated with them.
What I'm trying to do is set a query or flag of some sort if during any seven consecutive days a PERSON has samples associated with them where the summed amount is some value, we'll say 10. I have no problem summing an amount associated with a person over a 7 day period but am having trouble trying to visualize how to do this for any seven day period without having a sum for every 7 day period throughout history.
View 4 Replies
View Related
Jun 4, 2013
I have a database for training. I have a table of employees with one of the criteria being active which is enabled by a check box. I have a second table of all of the training data. When I add a new employee, or I want to schedule someone, I want to be able to have a combobox on a form which I can select the type of training and then with a push of a button find if there is an active employee which has not had this training through results of a query.
View 14 Replies
View Related
Jul 9, 2014
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
View 6 Replies
View Related
Jan 8, 2015
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
View 11 Replies
View Related
Apr 9, 2015
I am trying to get a count from a table, but only for a certain time period. Here is a little bit of my table:
STRMIDtrDateDT trDT
1617 3/25/2015 7:30:00 PM0
1530 1/8/2015 4:40:00 AM10
1532 1/12/2015 9:20:00 AM60
1533 1/14/2015 4:50:00 AM1
What I am currently doing is querying a table for the Count(*) for the day before. This works all well and good, but now I am wanting to break it down by a time range from the trDateDT field.
View 14 Replies
View Related
May 20, 2013
I want to make a query in my system which finds out if a room is available for a person to stay in. I want the query to show me a list of rooms that are available between a period of two dates. So if I enter a arrival Date (12/12/12) and then a checkout date (13/12/12) the query then shows me a list of rooms that are free for those dates. How would I go around doing this?
View 1 Replies
View Related
Oct 8, 2014
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
View 4 Replies
View Related
Dec 9, 2013
Any way to make the query expired after a defined period but it must remain with that query only not for other queries. Also the query expires should have an option to be alive again or redated for next period after updation means expiry means stop working but not corruption or anything else.
View 5 Replies
View Related
Sep 13, 2013
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
View 1 Replies
View Related
Nov 6, 2004
I was wondering if somone could clear something up for me.
I understand that if there is a database with for example:
table_Cars(reg_num)
table_Customers(cust_num, cust_name)
there is a many to many relationship and so the inclution of
table_Order(hire_date, reg_num, cust_num)
would eliminate this.
I understand this but where I get confused is what would be done if a customer could hire out more than one car on the same order.
a Car can have MANY orders and and order can have MANY cars.
back to square one.
any guidence on this subject would be appriciated!
thanks
View 2 Replies
View Related
Oct 24, 2006
Ok I know people are tired of code breakdowns but I got this code in the company's database and it is suppose to calculate 2 days before the mailing date. now to Text98 is not suppose to land on a Saturday or Sunday. The problem is in the code it is somehow ending up with the same date as the mailing date. I didn't write this and I have a module from the good people here that I use for my date calculation but I am not allowed to add to the company's database. Though I can only edit.
Can someone let me know why this code is pulling the same date as the mail date?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If WeekDay(Mail - 4) = 7 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 1 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 6 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 5 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 2 Or 3 Or 4 Then
Text98 = Mail
End If
End Sub
View 7 Replies
View Related
Nov 3, 2007
Hi,
I have the following situation:
I have two fields: Start Date and Stop Date and I need to create a procedure to break them down and create records.
Start Date Stop Date
01/25/07 03/05/07
Results:
01/25/07 01/31/07
02/01/07 02/28/07
03/01/07 03/05/07
Thank you in advance for any help.
View 2 Replies
View Related
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
Sep 11, 2014
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
View 5 Replies
View Related
Jun 16, 2014
I am trying to produce statistics reports in my database. The user selects the information they require in a form (date from, date to etc). A query then produces the results listing each job in turn which is what I want however at the end I would like to produce sub-totals for each client.
Depending on the criteria selected by the user, only some clients will appear in the list. Ideally I would like the footer to show something like:
Client 1 - 4 jobs
Client 2 - 1 job
Client 5 - 6 jobs
Client 6 - 17 jobs
I have tried using the field:
Code:
=Count([ClientDetailsName])
However this only gives the total number of jobs.
View 6 Replies
View Related
Apr 28, 2006
For a graph i have the below code sql to define the chart results
SELECT [Load],[Displacement] FROM [try];
try is a text box containing a table name within the access DB. how do i change the FROM sql statement to look at the name in the text box and use it as to draw the graph?
options in the text box are for example
TABLE1
TABLE2
etc....
If i use
SELECT [Load],[Displacement] FROM [TABLE1];
SELECT [Load],[Displacement] FROM [TABLE2];
all is well, i need to get it to look at the text box, well a combo box
View 1 Replies
View Related
Apr 28, 2006
For a graph i have the below code sql to define the chart results
SELECT [Load],[Displacement] FROM [try];
try is a text box containing a table name within the access DB. how do i change the FROM sql statement to look at the name in the text box and use it as to draw the graph?
options in the text box are for example
TABLE1
TABLE2
etc....
If i use
SELECT [Load],[Displacement] FROM [TABLE1];
SELECT [Load],[Displacement] FROM [TABLE2];
all is well, i need to get it to look at the text box, well a combo box
View 2 Replies
View Related
Apr 5, 2006
Hello Everyone,
I just migrated a back end to a network server. Now when more than one tries to use the database they get a file in use error.
Any ides?
View 3 Replies
View Related
Feb 2, 2005
I am new to Access, I know I couold do this easily in other DBs using a dependant relationship... but I can't get a handle on it here.
I have Client, a State and Coutry tables. What I want to do is assign a Country to a State (in the State table?) and then when I choose a State in the Client table, the Coutry field is completed automatically.
Any ideas?
View 1 Replies
View Related
Aug 17, 2005
Greetings to all, :)
i have a database with a few tables, but i need a special one that stores initial date and final date for a particular ID, but like this... :confused:
The table has the folowing fields; ID,STAGE,INITIALDATE,FINALDATE
New record:
ID1 - STAGE - INITIAL DATE - FINALDATE( leave blank )
New record:
ID2 - STAGE - INITIAL DATE - FINALDATE( leave blank )
When ID1 is repeated it must detect the blank field ( FINALDATE) , fill with the now date ( to finalize the record ) and also add a new record ( ID1 - STAGE - INITIALDATE - FINALDATE ( leave blank again ).
It also can have other records of other IDs in between.
I think i need a VBA code to detect the last record for ID#, detect FINALDATE field, write the date on it, save, and add a new record again.
Can this be done? :rolleyes:
Many thanks in advance
View 6 Replies
View Related