:confused: i am trying to develop a system which requires a date order to be used .. i want to use a filter query to filter out certain information from my tables into a form .. i know how to accomplish this but i am having a small problem getting there
i wish to set sunday to an order of one (1) and monday to two (2) and so on so that when i create my query i will be able to select the thursday for example ( by setting the criteria to a "5" )
i have a table consisting of the dates but i am unsure how to create a field that displays the order .. automatically calculating whether the date is a sunday or a monday and assigning the correct number as stated above, i have tried something (not sure what) which assigned a 1 to monday through 7 to sunday but this is not the order i wish to use and it failed to work with my query anyway
would this create this using my computers clock or something and if so what do i need to do to get this small part of my system up and running therefore enabling me to create my filter query
please help
thankyou :)
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
I have the following tables: Rates and SelectedDates, which are structured as follows.
Rates: RateID, EffectiveDate, Rate SelectedDates: Date. Table is bound to a combobox in which user selects one or more dates, mm/dd/yyyy, which is the same format as Rates.EffectiveDate.
I need a query to return the first Rates.Rate for the following conditions. (there will be a minimum of one record in the Rates table)
IF there is only one Rates.EffectiveDate for the SelectedDates.Date return the associated Rates.Rate
ELSE IF count(Rates.EffectiveDate <= SelectedDates.Date)>=1 return the first Rates.Rate where Rates.EffectiveDate <= SelectedDates.Date
ELSE return Rates.Rate associated to the Rates.EffectiveDate closest to the SelectedDates.Date.
I hope this is clear, as it is late and I am tired...:confused:
Below is the SQL I have on a Union Query. Each Query is based on a date range. The first Query date is Planned Immplementation Date. The second Query date is Revised Planned Implementation Date. If the Date in the Revised Planned Implementation Date is higher than the EndDateTxt Date Range I do not want the results to appear for that ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number] UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process] FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID] WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes")) ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:
DATE ID 01/01/2015 1 02/01/2015 1
What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.
I'm unsure what the easiest way to do this would be (or how to do it at all).
I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.
my code I am working on is:
Dim startdate2 As String Dim enddate2 As String Me.start_date = startdate2 Me.end_date = enddate2 Dim SQL As String
SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'
DoCmd.RunSQL SQL
I am getting a error on the sql = statement, I can't seem to get the statement right.
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like: =IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
This seems like a mediocre problem but I can't think of any way of making it work. I have a table with a bunch of fields in it and one of the fields is called 'user'. Now all the other values in the fields remain pretty much the same except the User name. I want so when I try entering a new record using a form, all the fields are already filled except the 'User' field, thus allowing me to just keep filling the table up with different user names while not having to fill up the other fields. Any help would be awesome. Thank you
Hey guys, So my boss needs me to do something with the form. I'll give you an example: Say the form/table has two fields Computer Name and User. Now one computer name can have multiple users and it can be any number of users (no set amount) she wants to be able to add one user at a time and not in the same field. She also does not want to have two different records with the same computer name but different users. The way I thought of doing this was to make a button in the form so when she needs to add more users to the form she can just click the button and another field would appear saying "user 2", "user 3"..etc. Is that possible or does anyone have a better idea? Thanks in advance for the help guys
Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...
Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.
i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.
I do hope this make's sense I'm starting to confuse myself.....
Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.
So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...
Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?
Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.
Hey I have a form/table that has the field name 'Type'..I want so that when a user types in 'Staff', he is able to type in a phone number, but when he typs in 'ISP' he is able to type in the name of the ISP. I have no idea how to go about this so any help would be appreciated. Thanks Yusuf
If there is someone out there that could help make the lane score sheet easier. or give an example you could send an attachment to joeschidek@hotmail.com
Overview: You are going to create a program that tracks the scores for a season in a bowling league. This program needs to be very easy to use. In fact, Access, SQL Server, or VB on top of Access is one good way to attack this program. Using a web interface to a CGI database is also acceptable. VB accessing text files will work as well. VC on top of Access/SQL Server is OK as well (wicked hard). VC has wizards that connect to databases just like VB. Check the Win 32 App Wizards.
The league meets every other week. The players bowl anywhere from 1 to 3 games (typically 3). Each week, the teams play each other and give handicaps based on total team average. The teams will win if their team total is higher than the other team’s total, including a handicap. With three games the league tracks wins/losses per game plus overall pins. This gives 4 games per week. The overall pins goes to the team with the most pins over the 3 games. As a note: the team names are the names of their captains.
The league bowls on lanes 17 through 20. This means that there are 4 teams with 5 players each.
Program flow v The program should perform like the following:
Ø User inputs the week’s data
Ø Program validates the week’s data
Ø User then tells program to update the season data
Program output requirements v Player data sheet
Ø Player average (sort by this)
§ Total pins divided by number of games
Ø Player high game
Ø Player low game
Ø Player high series
§ Highest total pins for a week
Ø Player total pins (subsort by this)
Ø Player number of games (subsort by this)
v Team Standing
Ø Sort by Wins, then losses <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< v Lane score sheet
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.>> Ø Two teams per player
Ø An entry for each player
Ø Three lines for scores for each player
Ø What alley the team is on
§ All teams must play on all lanes the same number of times in a season
§ You must determine a fair strategy to move the teams around
Ø The handicap
§ Difference in total team average
§ Whichever team has the lower average total will get the handicap
Ø Need a simple way to print different sheets for each week, based on the schedule
§ The code to automatically change the schedule based on the week is probably too much
§ A manual changing of team and lane assignments is OK
Program input requirements v Weekly data
Ø User should be able to input player scores who bowled
Ø Program should allow you to skip players that did not bowl this week
Ø Program should account for players that bowl less than 3 games
§ Example: a player with 12 games bowled going into this week, can only bowl one game
§ Once the stats are entered, the program will reflect 13 games, and calculate the average based on 13 games, not 15
Ø Program should allow inputting of which team won or lost
§ Be advised that this code can get tricky
§ I will accept, without penalty, manual entering of wins and losses
§ There can be a maximum of 4 wins per team per week
v Interface
Ø The user should be presented with command buttons, in order, to do their task
Ø They should not have to type in 50 commands nor have to open certain queries in certain order
Ø Ie: you are coding for a non Access geek, you may assume an intelligent person, not Brenda
Program calculations v Player average
v Hi game
v Low game
v Total pins per week
v Total pins overall
v Which team wins or loses
Ø You may force the user to enter this manually
Ø The amount of code to calculate wins/losses automatically is more than the amount of time to have the user calculate it manually.
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
i've just delivered my program to my client and there are 2 MAJOR bugs.
bug 1: the first time program is opened, everything works fine. it goes to the switchboard, etc., everything's cool. however, if he closes the program (i think he closed it using the red X) and tries to get back in, only one item on the switchboard shows up from that point on. the thing is, this problem DOESN'T HAPPEN ON MY COMPUTER so i have no way of even attempting to figure this out. the way i have my program set up is i have an interface and a BE and an .mdw file all in the same folder. i've created a shortcut that uses a workgroup switcher so that he can open the program and use it w/the .mdw file.
i have no idea what could possibly be causing this to happen. i would think that the switchboard would open correctly every time.
bug 2: this one does happen on my computer.
setup: i have frmCustomers (outer form) with sfrmJobs (subform) with a subform inside of that named sfrmFinances. Everything in frmCustomers works fine, and everything in sfrmJobs works fine. The primary key (JobID) in sfrmJobs is entered manually. After the JobID is entered, sfrmFinances is accessible. sfrmFinances' control source is qryFinances, which has a few fields from tblJobs (control source for sfrmJobs). frmCustomers is linked to sfrmJobs by CustomerID and sfrmJobs is linked to sfrmFinances by JobID.
problem: a new customer is entered in frmCustomers. a job number is entered for the new job in sfrmJobs (JobID). the user then attempts to update a field (any field, but for example, ContractPrice) and a window pops up and says, "Field cannot be updated". you click ok and it pops up again, at least 10x in a row. then it finally lets you enter data, but when you try to save the record, it pops up with another window that says,
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
excuse me?? how could there possibly be duplicate data??? the funny thing is tho, when the user closes the form, and then opens it back up and tries to enter financial data w/the same customer, it allows it.
???????
The first bug is more important than the second one, but any advice or ideas on either are EXTREMELY appreciated. thank you very much in advance.
this DB should transfer records from table to another table and vice versa, somehow i cannot make the other command button works. it took me a week to figure it out what is wrong with the code.
pls help me with my DB.
Somebody out there!
ill attached the DB if someone replies my request.
Hi all i'm really confused. I have tried to use the user-level security wizard to set my database security which works fine from my computer. However, when someone else logs into the database through my shared area where it is kept, they do not encounter any of my security settings that I set up for them. From this it looks to me that the security settings only apply when someone opens the database directly from my computer and not when they open it over the network from my shared area.
Could someone please explain to me why this is happening and offer a solution to it please. Your help will be greatly appriciated.
I'm designing an access system in which I have many reports (Primary Key is reportNumber) I need a form with a field in which I can speicify a range of reportNumber to print them.
example I need to print reports from 1000 to 2000
please note than I'm a beginner in this so I need detaild steps.
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
I have two tables tbl1 and tbl2 tbl1 has 10 fields named tbl1.id tbl1.field2 tbl1.field3 tbl1.field4 tbl2 has only three fields tbl2.field1 tbl2.field2 tbl2.field3
Now i need to insert values into tbl1:: tbl1.field1 tbl1.field2 tbl1.field3 from tbl2.field1 tbl2.field2 tbl2.field3 respectively, but i need to make sure if tbl2.field3 value is already there in tbl1.field3 then we don't need import those records. so we only need records if value of tbl2.field3 is not already there in tbl1.field3.
Please let me know What statement do I need to write so i can import all data from tbl2 into tbl1 by comparing as above.
I am new to this database stuff and my work want me to produce a database which will not only keep stock on printers, scanners, monitors and base units we have in stock but want an allocation system as well.
Basically, a member of staff from a department will make a request for some items. These could be a monitor, 2 base units and a scanner for their location. I need a system which tells the user that the items or qty they want is in stock or not. I know I need to use an unbound box for this but dont know how.
Then I need a form which a IT techie can allocate a job too themseleves and take the item to them once this has been done they sign off the job.
I have a rough idea on what I want in each table but I am really losing time and i need some help or if anyone can do a quick one for me with forms etc I will be ever so grateful!
The tables look as follows:
Dept - DeptID, Name of Dept Staff - StaffID, Name, Postion at college Request - RequestID, Date, StaffID Stock - StockID, Item (monitor), make (CTX), Model (17" TFT), QTY, Status (dead, working, faulty) Request deatils - ? - Basically this is where the staff member will make their request for what they want. Allocation - This part needs to be given to a member of staff and they needs allocating out Techie - TechieID, Name, Postion Location - LocationID, Location (where the item is going to)
If anyone can help me please post otherwise please please send me an email to mini_beest@yahoo.co.uk
