I'm looking for a way to create two feilds in a table, one for lbs and one for kgs. The data comes to me in both forms and I would like Access to calculate whichever field is missing. Meaning sometimes I can enter the weight in pounds and it will fill in the kgs and vice versa.
Is this possible and if so, how the heck do I do it?
Thanks in advance,
Sara
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
thos is f.i. a json data sting :
{"vip_kaarten":"0","reg_kaarten":"0","extra_vip_ka arten":"0","bedrag_extra_vip_kaarten":"0.00","extr a_reg_kaarten":"0","bedrag_extra_reg_kaarten":"0.0 0","vrjr_kaarten":"2","extra_vrjr_kaarten":"0","be drag_extra_vrjr_kaarten":"0.00","website_link_spon sorpagina":"1","website_banner":"","social_extra_m ededelingen":""}
How can i convert this to readle data?First string is the field name, second the value.
I am getting the following from a query
date|id1_|id2___|
1___|nick|james|
2___|john|nick_|
How can i convert this data this
id_____|date1|date2|
nick___|_1___|_2___|
john___|_1___|_____|
james_|_____|_2___|
Best Regards
I am using accdb but I wanna convert some datas into mdb, but I really don't know how to do this?
View 3 Replies View Relatedquestion about data entry and performing some mathematical operations before storing the entered data.
I have a data entry form where I want the users to put in some values but I want to perform a unit conversion before storing the data.
Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):
Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))
I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.
In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.
How do I record this automated results into my original table?
Thanks in advance!
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
View 7 Replies View RelatedI have a spreadsheet of generators with associated data: Make, Model, etc.
When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).
Example:
tblGeneratorMake
GeneratorID (PK)
GeneratorMake
John Deere has PK of 1
Kohler has PK of 2
If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
In a query I would like to extract the last ten years. This is what the data looks like:12/13 (Data type = text)(Short for 2012/2013 which actually represents 7/1/2012 to 6/30/2013.)
I am able to extract the "12" and turn it into the general number 2012 using: ("20" & Left([TAXYR],2))*1..But how can I convert that to the data type-date so I can include those records in the past ten years from todays date? I was trying to use DateAdd but I think the problem is the data type and where converting to date gives me "1905" or "9/##/1905." I get why it does that, but is there a workaround? Maybe adding 39,785 days?? (2014-1905)*365
I'm currently working on a project within access which will allow users to select/input information in a form which will then save to the database.
What I need is to create a button on the form which will allow you to convert the data collected from the form to a custom text file template.
(Or maybe the last row added to the database. - although this may cause errors. Example: the form information doesn't save which will in turn create a text file with the data collected from the previous job)
The custom text file template should have a sentence description followed by the value recorded from the form.
Example:
1 configuration type: (data from form)
2 model type: (data from form)
3 Poc: (data from form)
And so on.
So in summary - I would be to use the form to collect the desired information which will save to the database.
Once actioned, a custom text file should be populated with the collected information (with specific data placement as per the example above)....
I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?
Help me what is wrong with this expression:eek: :
=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))
I'm using Windows 2K SP4 and have just installed Office 2003. Everytime I open Access I get a message asking if I want to block unsafe expressions (presumably macros). How can I turn this off?
BTW I've downloaded all of the security updates, which are recommended by MS.
Hi,
Can anyone tell me why it is that when i have written a DB on one machine using Access 2003 and then subsequently put that DB on to another machine i get an error relating to unsafe expressions?
why does this happen and is there a away in which can stop this error message from appearing?
I am trying to implement the DB i have written at work but it does not look good when i get a constant error message appearing when ever i open the DB.
Thank you in advace for your help
Regards
Bev
1)I have a list of stock opening prices and closing prices for March. i need an expression or function that will calculate which company's stock price changed most during the month (difference between opening and closing price). 2)In the stock table, I have dates given to me in the following format mm/dd/yyyy. Lets say I want it only to display the date so it only shows the month but not the year of the day. What function will give me this result?Thanks
View 1 Replies View RelatedI have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:
1)Last Review date
2)Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)
3)Status
Now here is my question:
I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue.
Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?
I’m starting to wonder how I ever made anything work, before I found this place.
I built 2 queries and put them in a query
They share a common field
The join type is all record from Query1 and Matching from Query2
Next I added a count field for each
Then I added a calculated field that subtracts the count from Query1 to Query2.
So far it all works
When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.
i have a master table with various lookup feilds. i want to create a querie that creates a text expression feild called description and it is based on the info provided in the lookup feild. problem is when i create this querie from the location master it provides the id's from the lookup colums and not their text.
any solution
Hi experts
I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.
References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.
Numbers I specified like examples ig. were prescribed bye regulation.
If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.
It means I should use Condition Commands.
Thank You for help.
Is there anyway to use an if statemet to define the criteria for a query?
as in
if forms!RunReport!cboname <> "" then
query all names that equal forms!RunReport!cboname
end if
also is there any way to use a dlookup function (or another similar function)
to define several criteria for my query
ex.
i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"
thanks for any help in advance
Hi all!
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Thanks in advance, jon.
Hi all, I'm writing a database basically as a favour to someone. I'm a sql analyst/programmer so my access and vba skills are a little limited and I hope someone can help me with a problem I have.
Part of one of my forms is a flow chart style set of Yes/No questions and the user answers each question. For each question I have created a (hidden) text box which calculates a score (5 for no, 10 for yes) which I have done with a IIF expression in the Control Source of the text box. These are then totalled to give an overall score.
This score is then used to calculate a grade (A or B) and a percentage score using the same IIF method in the control source.
What I want to do though, is use this grade and percentage and write it to the individual record as data in the table - to use in reports and such like.
Is there any way I can do this? I've tried a few bits of VBA but to no avail. Is there a way that this can be done? Or have I approached this in entirely the wrong way??!!? :confused:
Any help would be greatly appreciated!
Hi,
Is there a way on Access that when a total is calculated using an expression on a form that it could update the table itself with that total?
Thanks for any help!
Roly
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
Thanks,