into the following data format (13 columns in total, with 7 rows):
Project Jan Feb Mar Apr May Jun Jul ... Dec
-------------------------------------------------------------------
A............100.................................. ..........................................
B.....................200......................... ..........................................
C.....................300......................... ...........................................
D...............................................40 0.........................................
E...............................................50 0.........................................
F...............................................60 0.........................................
G................................................. .........700...............................
Assuming today is January 1st 2007, the 2nd column must starts with current month.
If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have a table which keeps a record of days off sick.
The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format
and a [Last Day] = last day they were off sick, again in the same date format.
I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday" and then the last day is 27/10/06, it will produce "Friday" But, I also need it to produce "Thursday" (which would be the 26/10/06)
I have 2 comboboxes to select a product no, and a unit of measurement
I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL
However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.
(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)
I have actually got round this by testing the abs difference as being greater than a minuscule value.
ie If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error
I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?
so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem
function lookupmat as boolean
strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _ " and [pmpunit] = " & cboUnit
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.eof Then lookupmat = False rs.Close GoTo exitproc End If
'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same! If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _ "cboProd = " & Format(cboProd, "###.0000000000") & _ " Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _ "cboUnit = " & Format(cboUnit, "###.0000000000") & _ " Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
'and now both of these tests are showing a difference! of zero If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno) If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
Hi, I am looking for help with a database I am trying to set up for a dog club. I have created databases before although not for more than 6 years and am now probably at the bottom end of Intermediate in terms of skill and have no experience of using code builder as yet. I need to create a database of dogs, their parents, owners and breeders and a few other bits of information such that I can print a catalogue of dogs entered at dog shows (see below) and am finding it very difficult.
Owner(s), address, name of dog, sex (Dog or Bitch), Date of Birth, breeder, Sire - Dam
I've created various tables: Dogs, Owners, breeders, address, Dogs/Owners (junction table) and Query to do the pedigree of the Dog (ie the parents) which is a self join.
An owner can have more than one dog and a dog can have between 1 and 5 owners at the same time. Where there is more than one owner then the address is that of the first named.
I also need to be able to add and subtract both dogs and owners to/from their respective tables although subtraction will occur less frequently. I then need to be able to produce catalogues with the details laid out as above. Not all of the dogs or owners will be in each catalogue. I anticipate doing this using a query and then using mailmerge in Word.
I appreciate that this may be too big a task to help me with but do not like to give up until I have explored all avenues. If you can help then I can let you know the field and joins that I have and other important details.
I've got a small employee training tracking database - no problem there. What the users currently do is to run a report from the database that shows who received what training, and when. They then open Powerpoint, pull up a training certificate document and manually type the employee name (by referring to the MS Access report), course taken and date the course was completed.
Here's my question: Can Access automatically pull up or go into Powerpoint and produce these Powerpoint certificates automatically? If so, how would I go about doing this? The users would love me forever (or at least until the next request) if this can be automated.
Yhank you in advance - it's amazing how knowledgeable you folks are!!
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails): Fields: PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
I have a "date" field and a "Expire dare" field, what I need is automatically obtain a expire date wich will be the last day on the month of the date field plus 1 year. example date = 15/02/2006 expire data 28/02/2007
I'm wondering whether it won't produce only these records because of the design of my report (attached), with the Site Name under the SiteID Header...?? I wouldn't have thought it would matter, but...
My aim is to produce a report - using a drop down list (of customers from customer table) so that users can look at customer history.
I have created a form with a drop down list of customers - from customers table. I have used this to filter the results for a query on the orders table (gives me a history of that customer). All this works perfectly. I get all the transactions for that customer.
My question - is there a way I can create the same result but in a report format instead of the datasheet format. I.e the user will be given the data in report format when they select the customer from the drop down list.
I have produced a query, which counts the number of records fulfilling each set of criteria, but I can't now convert that into the matrix presentation.
I have a attached a copy of what is produced currently through Excel.
Basically each count on the query represents one of the boxes within the matrix. For example if the record Impact is 1, and the Likelihood is 1, then it would be counted within the bottom left hand box.
What I would like to know is this: I have produced a basic members table containing individual's details etc. How do I now produce an individual report for selected members (single page per member), in which I can determine which of the table details are included?
I have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.
The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.
What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails. It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.
So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.
Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.
I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.
I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.
I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.
In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.
Private Sub PurchaseOrderID_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String stDocName = "FrmPO_Received" stLinkCriteria = "[ItemID]=" & Me.ItemID DoCmd.OpenForm stDocName, , , stLinkCriteria End Sub
Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".
I have a database (accdb) out of which I want to generate accdr files of which user can only access / view the reports section ONLY. Looking for VB code which will generate employee wise (or any criteria) accdr files?????
I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.
The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.
The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.
Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).
This calculated referencing to the previous record is causing difficulties.
Per the instructions detailed here... http://www.fontstuff.com/access/acctut08.htm
...I created a parameter query in the form of a drop down box that, once an option is selected, should display a form with a number of fields pre-filled from a record chosen by the drop-down selection.
The drop down has a command button with the following code in the Onclick event:
Private Sub cmdCreateReport_Click() DoCmd.OpenQuery "qryUIRFollowUp", acViewNormal, acEdit DoCmd.Close acForm, "frmOpenUIRLookUp"
As it now stands when I click the button I get the form but none of the selected data is filled in. The fields are blank.
What code to I need to add to the above to make the form hold the data selected from the drop down?
I'm trying to produce a report which uses a static deisgn and does not change. For example,I require in the detail of the report a table say 3 * 8 ....and in the report I need the data to fill each one of these cells. However I may not have 8 records , therefore I need the remaing columns/cells empty - the design of 3 * 8 must not change... at the moment I have a dynamic design so if i have 3 records I have 3 *3 table leaving a massive gap on the report.
I am very new to access, any way that I can have a popup box appear when I ask to produce a report and I can type in a parameter of my choice and it will produce a report based around that parameter?
I have a table that is directly linked to an excel spread sheet that is updated each week external to the database. I have to produce reports on the data contained in that table.
I have already produced reports that look for specific number and those reports are produced automatically.
I was wondering if there was some way I could open a form and type a number into a text box and it would produce a report around that number. e.g. "list all engines below X margin" and I can type any number in representing X and a report would be produced.
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
