I have been working on customizing MS ACCESS to
produce a report that involves one mathematical
computation from my input data. However, I've run
into a problem because I can't get the program to
carry out a logic command that would work fine in
MS Excel: the "IF" command. Specifically, what I'm
doing involves a calculation, where one data input is
subtracted from another data input, but if the difference
is a negative number, I would like to substitute the value
of ZERO. The closest I've come to achieving this is to
enter the condition ">0" while in Query Design mode, but
the problem with this approach is that if the difference
is a negative number, the report does not display the
data that resulted in the calculation of the negative number.
In other words, I want the data to be included in the report,
but I want a "Zero" to be substituted for all calculations that
produce a negative number. For example, if I enter a set of
data, and the two numbers that get subtracted are 40 minus
36, then the difference is positive 4, and so I want a "4" to
be displayed; But if the two numbers to be subtracted are
36 minus 40, the result is negative, and so I'd want the
report to display a "zero" in the calculation.
Please advise me on how to set this up, as the only approach
I've found resulted in all negative calculations being omitted
from the report.
Thanks for your help.
Does anyone have a source that lists basic Access (2000) commands. I have training in SQL server (so I am just starting out) but I am working with Access and the commands aren't always the same and the help files in Access aren't much help.
My immediate need is to find the equivelant of the go command. I am trying to build one query with multiple update statements.
ID | NAME | Progresscomplete | Release 1 | a | 20% | July 2 | b | 65% | July 3 | c | 33% | July 4 | h | 15% | Sept 5 | i | 30% | Sept 6 | r | 5% | Dec 7 | s | 50% | Dec 8 | t | 15% | Dec 9 | u | 20% | Dec
now I want to create a query that shows the Progresscomplete for each task as a percentage for a Release
so the total progress for each release is July 300% Sept 200% Dec 400%
and the current level of progress for each release is July 39.33% (118/300*100) Sept 22.5 % (45/200*100) Dec 22.5 % (90/400*100)
thus the current level of progress for each task within a release is 1 | a | 16.9% (20/118*100) | July 2 | b | 55% (65/118*100) | July 3 | c | 28% (33/118*100) | July 4 | h | 33% (15/45*100) | Sept 5 | i | 66% (30/45*100) | Sept 6 | r | 5% (5/90*100) | Dec 7 | s | 55% (50/90*100) | Dec 8 | t | 16.6% (15/90*100) | Dec 9 | u | 22% (20/90*100) | Dec
I have queries for the first two not sure about the last also not sure if i should have the base of the last output be the current level of progress for each release or the total progress for each release
I have tried this for the last output
SELECT ID, NAME, ((Progresscomplete/Count(Progresscomplete))*100) AS currentprogresslevel, Release FROM TEST_RawData GROUP BY Release;
but get an error "tried to execute a query that does not include specified expression... as part of an aggregate function.
I have a report that takes money values form several records and sums them up in various category's making a list of departments with the total amount of balance for each (positive and negative numbers). I need to categorize the positive values form the negative and calculate the totals of each and the grand final total (invoice style)
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
What I need to do is to pull four subsets of data out of this table -
1) A unique list of courses that student x is taking in semester 1 (only) of a given year 2) A unique list of courses that student x is taking in semester 2 (only) of a given year 3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year) 4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)
Obviously there should be no overlap... and that's where I get a bit stuck
I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.
I think what I need is to have 2 queries -
a) pulls out all courses for student x in semester 1 for the current year b) pulls out all courses for student x in semester 2 for the current year
- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?
Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?
im designing a database for videoshop for loans in access. i have got my movies and loans tables (along with others) and have got them as a one to many (loans to movies) and have got Rental ID as Primary key in Loans table and got that linked to Rental ID# in movies table.
i want some sort of command in the loans form so that when a customer loans out a movie it will update the movies table (rented out field).
the fields that i have got in the loans table are:
Rental ID Customer ID# Movie Rented Out Date Rented Out Other Rental Details Rental Duration Daily Fine Rate
the fields in the movies table are:
Movie Title Movie Quantity Movie Description Genres Rating Director Popularity Rented Out In Stock Supplier ID# Rental ID# Stores ID#
if anyone whos reading this knows how i can do this then it will be highly appreciated if u replied to this thread.
Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
Im trying to run a query that looks in a table to see if product a and product b were purchased and then return the id but i never get any data, and I know there are some results that should be showing up. If I remove either one of the products it returns data but not when both are requested. here's the query: SELECT [Copy Of data].id, data.purchase_product, data.purchase_product FROM data INNER JOIN [Copy Of data] ON data.email = [Copy Of data].email WHERE (((data.purchase_product)="ProdA") AND ((data.purchase_product)="ProdB"));
I would like to issue a system command from within a VBA function. Specifically, I'd like to start another application using a command-line startup command, but am not sure how to do that.
Hi all, I was wondering if you could use custom menu bar commands to open reports and varying the recordsource of the reports according to which command you choose. For instance, if the user wants to view a report for January, he clicks on the January menu bar button. And I do not wish to use macros as there might be many macros to use.
Was looking at it and I am able to use my own vb functions but was wondering if I could use the tag field under the customise menu bar command to specify which records to filter to?
Maybe I should just use a form with all the code behind. lol... :p
I have created a query that has the info for a quick search. What I want to do is create a form that I type the last name of my customer and then click a button that opens the query and filters out all records that don't match the criteria I enter. I want the button on the form to do both. Is thie possible? If so how? Please Help. Very much appreciated
I was quite familiar with DOS commands and was able to create access DBs and write the VBA button code. However that was quite a few years ago and Ive forgotten it all.For every image file in DIR A or subdirectory If that image file exists in DIR B, then delete the image in DIR B.
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox PoundsPerOrder BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.
My question therefore; is the above possible? How would I go about linking this logic with the subreport
I have a custom menu bar that has a couple of menu lists, with a list of five custom commands that i want to run,depending on what is selected.I know you can assign a macro to each command,but is there a way to know which command has been selected. What i want to do is to have 1 macro that will run several functions depending on what was selected, rather than 1 macro for each button
1.Im wondering why there is no option to zoom in and out as we are working with relationships. 2.And even the option show all is not working; some of the tables in the relationship are kept hidden (screenshot is attached)! How could we work in this environment? 3.I do know the I can use the scroll bars, but they are not enough to perform the work more professionally
These features are available in most of the software!to zoom in/out To pan .To zoom all (extent Where are they in the relationship in Access?
I've been creating an automated way for users to import an existing DBF III file into an Access Table and then I'll be doing some other things after it's in. The user needs to be able to click a command button to open up a file picker, select the desired DBF and then click import and have it be imported into a new table.
Originally I had this all working because there was only one possible file name for the DBF file in each folder so instead of using a file picker I used a folder picker and supplied the file name in the VBA code as it was static.
However, I now need the user to be able to select a specific file and the name could be anything.
I've successfully edited the file picker to allow them to select a file however the importing is an issue. In order to do a TransferDatabase command I need the file path alone for DatabaseName and the file name alone for Source.
What would be the best way to split those two pieces of data up once the user has selected the file they need?
Tried to export a report to Excel using Tools>Office Links>Analyze It With Microsoft Excel menu. The order of the fields appearing in Excel doesn't seem to match the order on the Access report layout. What is the logic on how the fields exported? Thanks.
CompanyTbl: CompanyID (PK) Name Street etc. NAICS1(this is a standard industrial code, a 6 digit number telling about their product/service) NAICS2 NAICS...6 (company can have 1-6 NAICS codes)
A layman has no clue what these NAICS code are, so each and one of them has a descriptive title, like NAICS 32330 = Canned Vegetables Manufacturing For this puropse, I designed a second table:
ProductTbl ProductTitle (PK) NAICScode
Purpose:1. Be able to query by ProductTitle and get all companies with that profile. 2. Be able to retrieve Company ID with all its NAICS code/s and corresponding ProductTitle/s. Now one company can have up to 6 NAICS codes and one NAICS code can appear under the descirption of multiple companies. So a many-to-many relationship. But how? A third table, probably....But how?
In the highlighted line of code attached I am comparing two strings one from a snapshot recordset and one from an array.
The logic fails, you can see in the watches window both values equal "DESIGN and the elseif statement should execute but it does not.
The only reason I can see for this is that the values are equal to "DESIGN and not DESIGN. Where this single quotation mark comes from I do not know. It is not visible in the tables and it does not appear when the values are printed into excel.
