I'm trying to create a report for my organization that sums the amount of material used by date. For example, I have five separate locations in my organization that all consume the same material. It gets reported separately by location. I need to know the total material consumed each day. Then I need to sort that daily total from largest to smallest quantity used. I've tried to do it in a query, and also by summing/grouping in a report. I thought this would be fairly straight forward, but no success as yet.
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
I'd like to sum the amount for each date. Is this possible?
I will be updating the table with new dates and want to eventually create a form where i set the dates and it does the query where I get the amount for the dates specified, e.g. 01/07/20 - 08/07/20
In Access 2003 I have a form, at the footer of which I want to put some summary information. One of which is a count of the number of purchases over £500. I have tried many variations after searching the posts on this site but the only one which does not return me an error of some kind with the expression is
=IIf([Bought For]>"500",Count([Bought For]),0)
where 'Bought For is the reports listing of the purchase prices in a given date range when opening the report, but the value returned in my test is '0' when it should be '2' as the test file has two purchases of over £500.
I have several reports where the currency was always reported in dollars so for the fields in question I had a format of $#,###.00 which printed the dollar sign in front of the amount.
As the system has now become more sophisticated I need to print out a currency symbol depending upon the client. I have a currency table that links into the client so my query 'behind' the report holds the right symbol but how do I get it ito the report. I Know I can define a second field that will hold the symbol but to me this is a clumsy way of doing it. Is there anyway I can put the symbol in the control source for the data or into the format of the field or some other way?
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate John Smith$15$10$15$30$8 Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
What is the usual way to store the weight in a Bill of material database? Do you store the weight for each assembly and each component or only the weight of the smallest components (where the weight of each assembly is calculated by adding the weight of each component)? The way my table is set up just now only the weight of the cast component and screws are recorded and no values are recorded for the assemblies, I just want to make sure that this set up is apropriate as I wouldn't like to redesign my table once the system is up and running.
I'm new to Access (2002) and have been trying to learn by reading books, posts, forums and practicing with models. I've set up a database for inventory, primarily to track the cost of goods sold as raw material costs change from month to month. So far I have tables for raw materials, container sizes, suppliers, and finished products. But I have a problem - in some cases a finished product is also a raw material, so cannot appear in both tables. (Let's say we make cookies and we make ice cream, and we also make ice cream with cookies on top as a third/separate finished product!) Should cookies have two records ('CookieFinished' and 'CookieRM') and can they be linked together so cost is determined before it's part of another? Is there a better way? Thanks in advance!
I've tried searching the forum to no avail, I have a table (tblBarcodes) that has two fields, Barcode & Material The Barcode field is already populated with specific numbers, but what I need to do is when I enter a new material on my form it will assign it a barcode from the tblBarcode and then append the Material code to the that table against the relevant record.
Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).
Please see the attached table of materials checked out and materials returned. How to setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.
Also, I am a little worried about partial returns as when an employee only returns half the amount.
Maybe a strange question but what is the best way to display access forms in a PowerPoint presentation or in printed material. If i do a print screen and then tidy it up the resolution is not great. Any ideas
I have a form that could have duplicate material descriptions , is it possible to have on that form a field that counts the number of line items for that material and when the material changes the new number will appear .
I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date] and also, [Forms]![FrmDate]![Enter Type of Audit]
On each subreports On Load Event, I have added:
Private Sub Report_Load() Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#" Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#" End Sub
and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.
I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY. - If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY - If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.
However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?
I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013.
I have five subreports in one report, and each one ask the same information, Starting Date, Ending Date and Employee Name. I would like to have it so that I can enter the Starting Date, Ending Date and the Employee Name once for each of these subreports, then print. It is a pain in the butt to have to enter this information 5 times before the report will be generated.
I have a report on which I have two unbound text boxes that I want to populate with the dayname for a related date. One is called txtStartDateName, and the other.its txtEndDateName
txtStartDate name Control Source is:
Code: =WeekdayName(Weekday([txtStartDate]))
With very similar for txtEndDate..Now, the odd thing is that when I run the report, the day names that appear in these boxes are the day before the actual date - - - so if I used today's date (26/03/2015), I would get Wednesday instead of Thursday as it actually is?At first I thought it was my PC at work screwing around, but I have now tried a copy of the database on my new PC at home and it is exactly the same?
I have a script file of FoxPro 2.6 which is use to read amount field and write it in words. Can any body convert in VBA Script. I manualy write amount in words. Thankyou. Saira
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
Is there any way i can increase the amount of columns in a table before importing data? I am importing data from a notepad doc that needs abt 300 columns but i can only get abt 100 columns.
