I am using the formula:
Normal Pay: Sum(IIf([PAYCODE]="STD" Or [PAYCODE]="PAY_R" Or [PAYCODE]="RATE*",[AMT],0)) .
as an expression and need my query just to pick up one row with a total sum of these values, however I am still receiving 2 or 3 lines of seperate values.
Can anyone help?
Thanks
Paul
I have a Customer table with the normal customer info, name phone.Each customer has one or several parts. so I have a parts table.I have a service table and form.The service form contains the service number along with customer number.Each service has a service detail subform for service to the customers part.The problem I'm having and It seems to me to be an easy answer but it has escaped me totally. When I go to the Service detail form I want to pick the specific customers part.I cannot just select from the particular customer, but have to pick from a list of parts from every customer. I just want to see the parts from a certain customer that I have selected from the service order form.
View 3 Replies View RelatedI have a list of Vehicles in which each have their own maintanance schedules. Most of the maintanance for the Vehicles overlap. Instead of entering the same information over and over I'm assigning each type of vehicle a code number (example '05 Cargo is a 1, '04 Cargo is a 2). Now what I want to do is assign each maintanance the vehicle code, for instance I have 13 different types of vehicles that all need oil changes, so I want to assign the Oil change maintanance 13 codes. Is there a way to assign that maintanance 13 codes without having to enter it 13 times??
Thanks, Joe
Hi,
how could i get multiple values shown in 1 form only? i currently have a code to look through 6 fields and if the fields iar blank, you get a a message box. you press ok and the next message is shown for the next field if it is blank but it would be so much better to have 1 message box showing all blank fields required to be filled in.
all it does is check the fields as these are required before continuing. if anyof the fields are blank, then it tells you and you cannot continue untl they are all filled. it seems easy in my head!!
im sure this can be done but im not quite sure how.
any ideas would be greatly received.
NS
Hi All,
I'm a relative newbie to Access - I am wondering the best way to solve a problem. I am the admin for a database used to track projects and the quality control process. With this process we must keep up with the review of another database, the pieces of information entered which are erroneous and when the individual responsible corrects the data. To date, the errors have been manually typed into a memo field which is then pushed out to the end user in a report. The errors are very consistent and I want to create functionality that will allow me to select the errors (there are approximately 50 standard errors) from a list or using a button or whatever is wisest. Basically, I would love some best practice opinion here as I am totally stymied at this point.
Any advice for a weary admin?
Hi,
I am experiencing some confusion in setting up a many-to-many relational database, and I would greatly appreciate any assistance that anyone may have to offer.
Currently, I have two tables: one which is a last of organizations provided services, and the second table is a list of counties in the state. While many organizations only focus their services on one county, some offer services in multiple counties. The desired end product is a website in which users can search for available service organizations based on the county they have selected.
In Access, what is the best way to link each individual entry in the organizations table to the appropriate counties in which they offer services? For example, I will need to say Organization1 offers services in County1, County2, County43, and Organization 2 offers services in County2 and County62. When a user selects County2, it should return both Organization1 and Organization2… and so on.
What is a more efficient way to store this information than just having multiple columns?
Thank you in advance for your help.
I have a query, and one of the fields has a prompt for user input. Is there a way for the prompt to take multiple values?
View 1 Replies View RelatedCode:SELECT * FROM mytable ORDER BY type, value
hi i am trying to order a select statement by two columns, so it first orders by type, then by value. the above code would work in php/mysql (i think!) but not with asp/access. any ideas?
cheers
how to add times together from multiple subforms and display the data on the main form. Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.
My most recent attempt was to simply make an expression in the control source as follows:
=[Sunday subform].[Form]![Daily Hours]+[Monday subform].[Form]![Daily Hours]+[Tuesday subform].[Form]![Daily Hours]+[Wednesday subform].[Form]![Daily Hours]+[Thursday subform].[Form]![Daily Hours]+[Friday subform].[Form]![Daily Hours]+[Saturday subform].[Form]![Daily Hours]
Is it possible to have a text field in a table to store multiple values by using a comma or some other type of seperator? I do not want to set the field to a lookup field because the number of values that would be included would be to many to continuously look through to select. I would prefer to just manually type in what I need and use a seperator to create a multiple value field.
View 7 Replies View RelatedI am developing an access db where employees are allowed to load tools from a tool store.
I have three tables: tblEmployee, tblTool, tblOnLoan.
tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.
tblOnLoan is used to record which employee has what tools on loan.
When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.
When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.
i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.
Can anyone please help?
I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.
So for example
Column1 Column 2 Column 3
B11 Europe
would concatenate as:
B11.Europe.
Hello,
I am running a database in which end users input values into a table via a data access page. The problem is that there are usually lots of records in this table which requires the same value for a particular field.
I was wondering if there is a way to implement something so that the user can input the value once and the value is then copied to all the records?
Many thanks in advance
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
I'm working on a freebie for a day care center.
I can not seem to work this out in a single query.
I have:
tblChildren
tblParents
tblGuardians
Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.
Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.
each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.
So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.
Has anybody got any guidance for me please?
Hi,
This may be a dumb question, but I've got kind of a question about adding values.
I have a subform that contains a department name, and it's waste totals (in Kilograms) for the day, for a particular company. The company information is the main form.
Whenever I start a new department, the totals, and category names start blank (different departments could produce different waste).
I'm trying create a query that gives me the total value for each Category of waste. However, I keep getting all the seperate values returned?? As apposed to a total. For example.
Company A has two departments:
Office and Production.
For Office they had 20 Kg of plastic bottles and Production had 20 Kg of plastic bottles.
I want to be able to total the weight of plastic bottles...it's just not working../
?
Any advice?
Ok, i have a form in wich i want to set the parameters to a query.
On the search button it will open another form to display the data.
i used this code stLinkCriteria = "[ID]=" & Me![id] And "[status]=" & "'" & Me![sta] & "'
the 1st value for status is "active"
it returns a msgbox asking the value of the parameter and a field to input the data.
I can't understant whats wrong....
Hi all,
I was thinking/hoping of using a list box on my form to store multiple values, I haven't been able to find a way of storing any value so far so not sure how easy it will be?
How can this be achieved or is it just easier to use several check boxes (approx 8)
Thanks
Hello,
I am making a database and I am currently doing my forms. I have been using wizards and so far everything I have wanted to be able to do has been achievable, except for where I am now! I am making a 'purchases' form. What I want to be able to do is make multiple purchases in the one form. Let me try and give you an example to further explain myself.
Lets say Mr John Smith wants to purchase 1 black pen, 1 30cm ruler, 2 rubbers and a compass.
I want it so that rather than have to add each one of the purchases one by one, I want to be able to add them all in the same time and be able to go back to my purchases table and see:
Mr John Smith - 1 black pen
Mr John Smith - 1 30cm Ruler
Mr John Smith - 3 rubbers
etc
Hopefully you understand what I mean?
Can anyone assist me?
Most appreciated!
Adam
Query 1:
Field #1: User Name
Field #2: CountOfUserName
Query 2:
Field #1: User Name
Field #2: CountOfUserName
Query 3:
Field #1: User Name
Field #2: CountOfUserName
Query 4:
Field #1: User Name
Field #2: CountOfUserName
I want to create a Query that will add
[Query 1].[CountOfUserName]+[Query 2].[CountOfUserName]+[Query 3].[CountOfUserName]+[Query 4].[CountOfUserName]=
[My Query].[TotalCountOfUserName]Group by UserName
It seems difficult.
I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!
I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials.
I have three Tables: tblX, tblY, tblZ.
tblX stores details for each individual material. Its structure is as follows:
tblX
XPK | ID | value1 | value2 |
01 | A | asdfas | asdfdf
02 | B | dfasdfa | sdfaf
03 | C | asdfffd | asdfg
(here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)
tblY stores materials that are combinations of two primary materials:
tblY
YPK | 1st | 2nd | ID | value1 |
101 | 01 | 03 | M | keiury |
102 | 02 | 03 | N | kjgeiih |
103 | 03 | 01 | P | djlkgoi |
(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)
tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:
tblZ
ZPK | 1st | 2nd | 3rd | 4th | ID | value1 | value2 |
1001 | 01 | 03 | -- | -- | R | asdprw | mnvd |
1002 | 01 | 102 | 02 | -- | S | adsfd | oirtyr |
1003 | 103 | 02 | -- | -- | T | werwq | pojfgr |
1004 | 02 | -- | -- | -- | U | alkfdp | uioite |
1005 | 01 | 02 | 103 | 102 | V | keqwei | oirewj |
(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)
So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...
I hope I am clear enough till this point.
My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:
1) save each primary material from tbltemp to tblX
2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.
I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.
I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).
If you are still reading, I already owe you a ton of thanks!!
Hi All
I have a list box. Below it lies a text box.
I'd like to click on multiple values in the list box which then populates a text box with each value to form a sentence.
currently my code looks like this
Dim Comment As String
Comment = Me.lstComments.Column(0)
Me.txtComment = Comment
Using the above method only inserts one value. How can I modify this to insert multiple values into the text box?
Much appreciated
I am trying to do DSUM as a control in the footer of a report which would sum up values in one field [Charge_Qty] only if certain criteria are met for another field [CPT_HCPCS_Code]. The problem is that I cannot use the proper syntax for the multiple criteria but am successful if I only have one value in the criteria. ok this works below but I need to use multiple values such as 93303 - 93321, 93662, 93350-93352
=DSum("[Charge_Qty]","DIVISIONQueryDATEYEARTEMPLATE","[DIVISIONQueryDATEYEARTEMPLATE]![CPT_HCPCS_Code] = '93303'")
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?
I have a table for keeping track of software keys This table has all the software keys and an ID field in it.
I then have a table for computers that currently I have it set up so that there is
OSID Field linked to SoftwareID
OfficeID1 Field Linked to SoftwareID
OfficeID2 Field Linked to SoftwareID
On a form I would like it to list ALL software no matter if it is linked or "installed" and show for those that are installed the ComputerID that they are installed on.
Currently no matter how I set it up I can only get it to show the ComputerID for the OS only or for the OfficeID1 only. I want to see if the software is on a computer no matter if it is in the OSID, OfficeID1 or OfficeID2. How do I do that if it is possible?
This particular database generally looks at reporting individual records as opposed to summary reports. So for a particular data record i have the following data [x1][x2][x3][x4][x5][y1][y2][y3][y4][y5]
I need to chart (scatter) these figures as paired x,y variables for display on a feedback report such as
x1,y1
x2,y2
x3,y3
x4,y4
x5,y5
For some reason i simply can't wrap my head around haw to make this happen.
Many Thanks in advance for any help.
Cheers
ndeans