Retrieve Figure Using Query?
Jun 14, 2007
Hi,
i have got a database which consists of table, queries, forms and reports.
I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one.
ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields,
If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean
Ref No Event Date Balance_BFAdjustment Balance
06/00014 31-May-06 115140-5,757.00 $109,383.00
06/00014 31-Aug-06 109383-5,757.00 $103,626.00
06/00014 30-Nov-06 103626-5,757.00 $97,869.00
06/00014 28-Feb-07 97869-5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
Is there a way to do this using query?
View Replies
ADVERTISEMENT
Nov 15, 2007
im tryin to get this query to get data from current weeks inputs. ive googled and tried everything with no luck so far. so here i am. can someone tell me what im doing wrong? keep in mind i dont know what im doing.....
heres what i put for criteria under my date column.
Between Date() And Date()-7
doesnt work.... anybody? thanks in advance...
View 10 Replies
View Related
Jan 17, 2007
Alright people... I'm ashamed to even ask but here goes.
I have two tables:
1) Employees
2) Neighborhoods
Table Employees has a comprehensive list of all employees and their information (including phone numbers).
Table Neighborhoods has lots of information about each neighborhood we're working in and it also specifies what people play certain roles in each neighborhood. For instance, there is a field for "Field Manager" and "Sales Consultant". These two fields are exact matches of "Full Name" from Table Employees.
All I want is for my Neighborhoods Table to be updated or a new table be created that imports the "Field Manger Phone Number" into the Neighborhoods Table and the same for the "Sales Consultant".
-----------------------------
To be explain one other way, this is what I want:
Table: Employees
Field: Full Name
Field: Phone Number
Table Neighborhoods
Field: Field Manager (already equals Full Name)
Field: Sales Consultant (already equals Full Name)
Field: Field Manager Phone Number (This is the field I want pulled from Table Employees)
Field: Sales Consultant Phone Number (This is the field I want pulled from Table Employees)
-------------------------------
I've tried my best to create an Update Query or a Make Table query but I have not gotten any good results. Can someone please help me out with this being very descriptive as I am certain I'm just goofing one simple step.
Thanks in advance.
View 4 Replies
View Related
Feb 7, 2007
I'm trying to build a form based on a query to use for data editing/entry purposes.
The only way I can think of to do this is to use a query to get data that is particular to my specified customer.
The first problem I have run into is that one query does not contain enough fields to retreive all the fields in my table.
I figure, perhaps I have made a flaw in my design and shouldn't go further with this until I verify it to be true. I don't think I've made a flaw, but the fact that a query has limited maximum number of fields leads me to think I might have.
Each record in the table I'm querying contains several pieces of numerical data that might be recorded during a service visit.
Obviously I could query the table twice but that doesn't feel like the right way to go about it.
View 3 Replies
View Related
Oct 19, 2005
I have a query and using a [text string] as criteria in the Design Window - when the query runs, entering the appropriate search string retrieves the appropriate results. I have created a form using this query as the recordsource - and as such the user is asked for the string before the form loads and the results are displayed.
Is it possible to retrieve the string the user entered and display this value on the form?
View 2 Replies
View Related
Nov 4, 2005
Hi all,
I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.
I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)
I want to be able to retrieve, say, the first 25 records on that table.
Any help is appreciated
View 2 Replies
View Related
Apr 18, 2007
4/11/2007 1:01:28 AM
4/12/2007 2:02:52 AM
4/13/2007 4:21:30 AM
4/14/2007 2:22:21 AM
4/15/2007 3:34:53 AM
4/16/2007 4:02:37 AM
4/17/2007 5:03:15 AM
4/18/2007 7:17:34 AM
I need some helps
I have a table with date + time, how can I query so that it retrieve a 7 days range from today and go back ti 7 days
i though this should work (it works if the the table has date only no time)
Between Date() And DateAdd("d",-7,Date())
thanks
View 2 Replies
View Related
Apr 27, 2007
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
View 2 Replies
View Related
Feb 21, 2014
How to retrieve data from a table (via query) ? I created the below query, but I'm not sure what else is needed to retrieve the value from my SQL query. My query code is below. I'm not getting any errors.
Dim strClient As String
strClient = "Jerry Davis"
strSQL = " SELECT [Progress Tracking].[Client Name], [Progress Tracking].[Client Start Date],
[Progress Tracking].[Start Body Weight], [Progress Tracking].[Tracking Date]
FROM [Progress Tracking]
WHERE [Progress Tracking].[Client Name])= ' " & strClient & " ' "
MsgBox "Weight Box " & " " & strSQL
View 12 Replies
View Related
Apr 14, 2014
I'm trying to retrieve data based on the contents of one column.
Sample table
Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111
gfdsffsdf 1006 W5464111
gfd 1005 N7765111
gfdsffsdf 1006 A4000011
gfdsffsdf 1006 W5464111
ssdad 1001 2378797
gfd 1002 1164478
ssdad 1001 965000
gfd 1002 780000
yjgk 4456 540000
kjhkh 2009 150000
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not
IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
Third query not working
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
View 5 Replies
View Related
Mar 17, 2014
I am trying to create a Database that will type our orders. I have a table with our customer list that includes both billing and shipping information. The problem is that sometimes one customer will request a "drop shipment" to another customer. Is it possible to retrieve different data from two different customers? Billing info for customer "A" and shipping info for customer "B" without creating seperate tables?
View 1 Replies
View Related
Aug 19, 2013
Is there a way to retrieve the value of a user-defined type in a query?
Here's the type:
Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type
I have a function with the following excerpt:
Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function
In my query I expected to be able to put the following:
Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount
However, Access didn't like the period in .Rank or .ClassCount.
Should I just write two different function to get 'rank' and 'classcount'?
View 2 Replies
View Related
Aug 12, 2015
Current situation is that I have this form with a chart.
Row Source :
Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;
PolyWrongRegInsCount; <- This table is one of the queries that I created.However, the data is manually added. I want to get the data from excel spreadsheet. And load into my queries
View 2 Replies
View Related
Aug 22, 2014
When I attempt to use the me.field = me.combobox.column() method to retrieve info from another query,it only works if I use column 0 or 1... it doesn't work with higher columns number.
- field name: cbofi
- row source: listado-unico-fi <-- it is a query with unique values and 7 fields in total
- event procedure, change:
Private Sub cbofi_Change()
Me.txtDOCTPT = Me.cboFI.Column(1)
Me.campovia = Me.cboFI.Column(5)
End Sub
- txtDOCTPT is the field that indeed works
- campovia is the one that does not work
I tried using column(0) and column(1) and it worked fine... but higher column numbers do not work. Just mentioning because the field names are all right.
View 3 Replies
View Related
Nov 26, 2007
Hi,
I'm creating a new database for an investment brokerage company. The company invests clients' funds for a specific length of time and pays out a return on their investment on a weekly basis for the duration of the investment.
This is my first real database so I'm gonna need all the help I can get. The company has some pretty huge requirements but right now I'm focusing on phase 1 which will be very simple.
Right now I'm working on the database design but there's been one thing which has been bugging me for a while so I figured I'd ask you guys to help me get my head around this.
Let's say we have a table called Investments. This table will include data on the amount invested, the number of weeks invested for, and the ROI percentage. For example...
Investment Record Example
Amount: £10,000
Weeks: 10 weeks
Percentage: 10%
One of the requirements of the database is to produce a report that will list each week of the cycle and the amount paid to the client. This will require calculations in order to produce this data. Here's an example...
Payment Report Example
Week # | Payment Date | Payment Amount | Notes
1 | 1/1/01 | £100 | Enjoy your first payment! :-)
I can see how this can be easily done by producing a query and then a printing a report based on it. However, here's the tricky bit... for various reasons, the payment date or the payment amount for a certain week may need to be modified, and a note value may need to be added for particular weeks.
Of course, I could just create another table called Payments to contain this information, but I don't want to have to enter the data manually. It would be a lot more productive to have the database calculate these values, but somehow let the user modify these values, if necessary. That's what I need to figure out!
Can someone advise me how this can be accomplished? I've been reading up on the Make Table feature, will that do the job? Note that I do NOT want to create a payments table for every client so I'm quite sure.
I look forward to reading your replies.
Thanks in advance.
Warmest,
Zahid
View 5 Replies
View Related
Feb 11, 2008
Can someone explian to me how the max function works!
View 5 Replies
View Related
Jun 30, 2006
This is probably a long shot, but I figured I'd post it anyways. We have a form that is used to input customer data. It was written about a year and a half ago and uses some programming in it. It's worked fine on the people's computers it was originally installed on, but recently we've tried to put it on some of the newer computers and when we run the form, it just freezes Access up instead of going to the form log-in screen. The weird thing is, it works on some of the new computers. They all have the same version of windows and the same version of Access, and the code in the form itself hasn't changed at all. I was thinking maybe the form was looking for something that wasn't there, and freezes because of that. Anyways, any ideas would be appreciated.
View 1 Replies
View Related
Aug 9, 2006
Situation: I have to seperate tables(table A and B), both have a part numbers field, manufacture field, and manufacture p/n fied. But only one of the tables(table B) has manufacture and manufacture p/n filled in. Also, some part numbers are missing from table A that need to be in B and some are missing from B that need to be in A.
Goal: My question is how do I make a query or whatever i need to tell access to find all the manufacture and manufacture p/n info(related to its designated part) in table B and put it into the fields in table A respectively. I guess i'm basically trying to join two fields in two different tables, i mean i need to the manufacture and manufacture p/n copied over to its related records in table A.
The reason for this is because we are migrating from Quickbooks to a program called Shoptech E2(access based) to do all our supply chain management. Things were correctly imported from QB except for the fact that the manufacture and manufacture p/n was not imported for some reason. Now E2 only looks at table A to pull its info and does not see the relationship, thats why i need manufacture and manufacture p/n copied over to table A to its respective parts.
View 9 Replies
View Related
May 20, 2005
Hi
This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
View 1 Replies
View Related
Oct 21, 2005
I have a query in which I need to display just the data that was entered from 9:00 p.m. the previous day until now. There is a date/time field called "QtyTime".
Thanks.
View 2 Replies
View Related
Jan 16, 2007
hello
i have the following expressions:
Status: IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing",Iif([LastReviewDate]>=#11/1/2006# And([LastReviewDate]<=#10/31/2007#,"Complete")))
when i try to close my query and save i get the error of the expression you entered has a function containing the wrong number of arguments??
any ideas on what i am doing wrong??
View 1 Replies
View Related
Sep 8, 2005
OK, this form has a series of buttons that lead to other forms or open up into reports. The problem is I can't figure out how they do that. On the click event procedure of one of the buttons it says =HandleButtonClick(1) .
This is NOT a macro.
I don't think it's a command button either usually those say [Event Procedure] in the click event.
I can't figure it out. Anyone have any suggestions please?
View 2 Replies
View Related
Apr 7, 2008
First time on here and I am looking assistance with the last part of this code. Can someone tell me what this is looking for thanks...
ExlFile.Application.activeworkbook.SaveAs "........Compliance Reports" & Rtn & BU & "-" & Cat & ".xls
View 2 Replies
View Related
May 3, 2005
First of all, thanks for any help in advance, I really appreciate it. This is my first big project in Access. I am trying to design a database for timesheets for all the departments at work. We have 21 departments with various numbers of employees in each department. At this point, I would like this database to be able to have users enter their own hours each week and print their individual report of hours worked then designated users add employees, look at previous payperiods and print out a master report as well as past reports. One pay period here is two weeks, with the days starting on Saturday, though I would like the database to keep track of one week at a time and then be able to group two weeks together to make a pay period. Each employee can work regular hours in a day and can also take up to 6 type of leave plus work overtime. So there will be 8 types of hours that could be entered. So far, I made tables for each different type of hour and created fields for each day of the week and created relationships to the employee table and pay period table. I made a form in datasheet design, but it didn't work out well. For lack of a better thought for them to enter their hours, I was going to design the form to look like this (but with spaces like a table);
Employee(fn) Employee(ln) Payperiod
Sat Sun Mon Tues Wed Thurs Frid Totals
Regular Hours
Vacation Used
Comp Used
Sick Time
Overtime
Holiday
Unpaid Leave
Paid Leave
The only thing is that I can't get the forms right and it seems like a lot of wasted space since employees will rarely take holiday, paid leave and unpaid leave. Is there a better way to design this?
I also have to take into account security. I eventually have to be concerned about departments only having access to their own records and employees within those departments only being able to access their own hours (except for designated users to do the master sheet). I haven't had any training or experience in access database security. Is there a way to limit records in a table or would I need to create a seperate database for every department?
I am attaching my table relationships in case I didn't describe this very well.
Thanks again for any help. If you need any further information, let me know.
View 1 Replies
View Related
Nov 9, 2007
Hi to everyone. I am trying, have been trying, will probably kill myself trying...to figure out the best way to make tables/relationships with the information I have already imported into my db. If someone could please PLEASE help me, I would appreciate it. I'm a visual person, and I have so many pieces of paper around my desk that I can't even see the top of it anymore!!!
Background info, I am building a db for employees and courses they MUST take. However, there are 60+ courses, only 4 of those courses are frequently taken (the others are more "train the trainer" and such). But which of those 4 courses you need to take depends on whether you're a supervisor or tech. So if you're a supv, you need to take courses 1, 2, and 4. Tech, you need to take 1, 2, and 3. My "dream" is when I later make a form, I can get the boxes to darken for the courses that don't apply to the employee.
Attached is a print screen (sorry it's .doc but I'm limited with the programs on my PC) of my current ONE table in design view so one can easily see how many fields there are.
Now, I realize to normalize, it's ideal to have only ONE subject matter per table. So, in a dream world, the tables that SHOULD be created to be completely normalized are as follows:
T_Employee
LastName
FirstName
MiddleName
SSN
T_EmpDetails
RNK
Supv
PPOCCGradeStep
EOD
DOBbasdSCD
T_Job
ParaLine
Para
T_JobDetails
Unit
DutyLoc
Activity
PositionTitle
Type
T_Courses (contains date course taken, IF taken)
NSPAE
NSPAS
.......etc..
So, hopefully you can see why I'm having such problems. I know that I could only create lookup lists for "Type", "Para", and "Rank". All other fields, although some do have repeating information, would result in HUGE lookup lists of probably more than 200 things.
Does anyone, anyone have any ideas??? Please!
View 10 Replies
View Related
Dec 29, 2007
I have no idea WHY I can't seem to figure out how to construct relational tables, but I just can't get it! I've built 2 other databases, but always had to come here for help on making the tables. So here I am again, only this is a bit more complicated than those I've posted about before. So I apologize for the length, but I'll try and post EACH field now so whoever may answer can possibly understand my frustration and confusion. The fields I need to have are as follows (separated by categories to shorten length here):
SoldierLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
SSN (#)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
Ht (#)
Wt (#)
DOR (date)
PEBD (date)
APFT (date)
Blood (txt)
Meds (txt)
Allergy (txt)
PHA (date)
Mobd (yes/no)
93 (date)
8286 (date)
Tags (yes/no)
Profile (#)
FCP (txt)
MomLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
DadLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
Deceased (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
SpouseLastName (txt)
FirstName (txt)
MiddleName (txt)
MaidenName (txt)
Suffix (txt)
DateMarried (date)
DateDivorced (date)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
ChildLastName (txt)
FirstName (txt)
MiddleName (txt)
Suffix (txt)
DOB (date)
Street (txt)
City (txt)
State (txt)
Zip (#)
EmployerName (txt)
Supervisor (txt)
Street (txt)
City (txt)
State (txt)
Zip (#)
LastUpdate (date)
SoldierPhone (#)
SoldierNOK (#)
SoldierAlt (#)
SoldierCell (#)
MomPhone (#)
DadPhone (#)
SpousePhone (#)
ChildPhone (#)
EmployerPhone (#)
Ok, now perhaps someone can at least see where I'm coming from...or perhaps I'm making a mountain out of a molehill...it's possible. But the hard thing to remember is that one soldier can have more than one mom/dad (with divorces and such), more than one child that may live at more than one address, as well as more than one spouse (former and current), and last, more than one employer.
I'm pulling my hair out...strand by painful strand...trying to figure this out. Can anyone, ANYONE please please please help?!? I'd be soooo appreciative of any advice!
View 6 Replies
View Related