Is there any way to take a "denormalize" it on a report. I have a two table that have a many to many relation, say companies and projects. I want to show a report that shows the company name and next to that row shows "Project A, Project B, Project F".
View RepliesHi,
What I have to do is import dbf files, massage the data, and then export them out again as an xls or dbf. I have a Main table that is imported that contains the company_id, address, city, etc. There is one record per company_id in this table. Then I import another table called Person which contains only the company_id, contact name, and contact title. There are several records per company_id in this table. There could be upwards of 100 contact names for one company_id, each dbf is different. I then combine both of these tables into a new table or query using the company_id as the Primary Key. Most of our customers request that we flatten the personnel into the Main table so there are no duplicate company_ids and all the data for a company is in one record. That's why I have to figure this out. Right now my company uses an old FoxPro in-house written program to flatten the personnel file. I am trying to convert us to Access and Excel for work order purposes. I have found some code to help me do this, but I am having trouble modifying it to fit my needs. I am attaching a small mdb that contains the table that I need to flatten and a module with the modified code. Could someone please take a look at the code and tell me what I am doing wrong. I am new to VB and not too sure of what I am doing. I need the contact names and titles to have Person01, Title01, Person02, Title02, etc. as the field names. Thanks for any help given.
I have a quality control database that has a QCEntry table that contains information about each sample the QC technician takes from production. This table has a one to many relationship with the TestResults table, where the tests performed on the sample and their results are stored.
QCEntry table is structured like
Code:
EntryID Product Lot Number Day Time
1 AB-500 121323 12/23 5:00
TestResults table is like
Code:
ResultID Entry ID TestName TestResult
1 1 Carbonblack 50
2 1 MFI 10
My question is: Is there a way modify large amounts of data like this using a query or some other method to look like this? Kind of denormalizing the tables?
Code:
Product Lot Number Day Time Carbonblack MFI
AB-500 12323 12/23 5:00 50 10
I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.
I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.
The code I am using is as follows:-
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
[code]....
How do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
I have an asset data base to generate individual asset detail reports with a sub report on the same page listing similar assets from a separate query. I have set the master and child fields, one to many. This works perfectly for the first two assets (pages), however the sub report stops showing data on the third asset (page).
View 13 Replies View RelatedSo I have a text box in a report that I want to pull in data from a field in another report, so the 'control' for my text box looks something like this...
=[Reports]![rptSalesReceiptSub]![Text141] (i.e Text141 is the data I want to pull into my report)
...the path is completely correct but when I run my report I get.#Name? If I run the report that has the data I want, it's fine (in other words Text141 has valid data in it)
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
I have a database with a form called "Main" where users input data and then print a report from it. "Main" has fields in it from another form "Members". This data (from "Members") is shown on "Main' by Dlookup coding, and therefore cannot be selected for input by the user. Now, lets say a user inputs data into "Main" and prints the report on 12/30/2012. On the next day, a member's name is changed and I update that data in the "Members" form. On 12/31, I would like to print the report again, but it shows the updated member's name instead of what is was like on 12/30. How can I keep the old data in case I want to print the report in the future like it was initially printed? What do I need to do to any form(s), report or what VBA code needs to be written?
View 3 Replies View RelatedHi all,
This may be dump question. Don't laugh at me..Can I display the data from two tables in a report without using a query. Why I need this because I had two tables which has no common fields in that... Or if there is a way how can I do that....
thanks in advance
Thanks
I am trying to create a tax report for my business and am not very familiar with Access beyong making basic reports from a single table and query.
Here's the problem:
I want to report total costs for my unsold inventory purchased and worked on prior to 2005. From that I want to be able to subtract the total costs of sold inventory purchased and worked on prior to 2005. Yes, all in one report. Is this even possible?
My report contains these columns from a single table-
inventory #, purchase date, cost, sold or unsold (yes or no check box), 3 columns of additional costs and 3 columns of cost dates. Also a couple other confusing columns of costs that were split between myself and co-purchasers. These include a column of negative costs, the date paid, the balance paid to the co-purchaser, and the date of the balance paid.
I have been sorting by purchase date prior to 2005 and unsold.
The problem is I have no idea how to include the values of the sold inventory in with this report of all the unsold inventory costs without creating a massive report with all my inventory from which I can not sum the columns properly.
The other problem is that when I sort by purchase date I get cost dates from after 2005. I can limit the criteria for one of the date columns, but not all.
My questions are:
How do I included both sold and unsold inventory information in a report and sort by inventory # without simply listing all the information together in one column by inventory #? Can I do the calcualtions I want to do in one report?
How can I limit all of the date columns to show only before 2005? If I make each of the date columns with criteria AND >#12/31/2004#, they limit down to only a few records because many of the date fields in my database are empty. If I use the OR criteria it doesn't work.
I actually have many other questions but I suppose those are the biggest ones.
I know that this is a very specific topic, so if anyone has suggestions as to great resources for Access help (ie. books, tutorials, etc.) particularly with creating tax or inventory reports from a single table, that would be extremely helpful.
Anything would be extremely helpful!! I'm sunk.
Hi all, let me start by saying that i am not that experienced with access but find it really enjoyable and want to learn more. i dont always understand or use the right "jargon" but here goes. i have a db with 3 main tables, each table has the same layout and info inputed into it (ie, name, address, product, ref no. etc.). from each table i can print a sales reciept with all of the relevent info on it. i also print a sheet (report) with the customer names & addresses on it, used as the postal labels. i currently print 3 postal label sheets, 1 for each table. what i would like to do is print just the one report but taking the name & address data from all 3 tables. would really, really, really, really appreciate any help on this as it is beginning to frustrate me, and waste a lot of paper !!.
i'm using access from microsoft office professional plus 2007.
many thanks
dbwannabe
ok I know some will find this easy but I write reports in VBA not in Access.
I need to teach someone to generate reports that will bring in all the fields for a table but only for the name he provides in the "Party" field. I currently have a query (and a report) that brings in the records in order of the Party field but I need it to pop up a input box to have him type in the specific party he wants the records for that party only.
How do I get it to give a prompt box and limit the report to the party inputed?
In the production report that I made, I want to achieve 2 things.
1) I want the report to filter automatically to display only the current month's invoice. As you can see, all of the invoices are displayed from 2012-2014.
2) I also want to be able to filter the report based on the invoice number. I want this to be achieved by clicking the invoice button on the top part of the report.
Hey can I transfer data into a report wizard, to have the wizard use that data instead of the data in a table or query?? This data would come from a form.
View 14 Replies View RelatedIve been pondering on this problem for far to long now so decided it was time to ask here !
I would like to return (based on a query) some records that would populate a report. I can set the source of the report as the query but my issue is that I need to actually have some code prior to report population.
EG if they have two products I want to list them product1/ product2 and as far as I know this isnt possible when you just set the source for the report...
The data is coming from SQL server so I have the option of a stored procedure but from trsting that seems to make things even more confusing.
Any help appreciated as Ive been stuck on this for far too long !!
Phil
Hello,
I have a time and billing database that contains a form where the user enters a beginning date and an end date and then must enter an invoice number. All this information will appear in the header of a report. The invoice number will always be different. How can I get the invoice number to print on the report? The beginning and ending date is working just fine, but I can't get the invoice number to print. I think I need to have whatever number is entered to be temporarily stored to a table, but I am not sure at all. I would appreciate any help given.
Thanks.
Hi everyone,
I am having a little trouble using a 'canned' Access database. Using the built in Asset Tracking database, I am trying to get specific information on the Assets report. The Assets report generally shows all assets, I've made a new report to show the data sorted by the phone extension number, also the room number (which is how our inventory is tracked).
This works well, except, I want to be able to view one extension/room at a time. To do this, I added a button on the 'Enter/View Emplyees' form to preview the report, and created a macro to get this info. However, it only shows whatever data is highlighted in the emplyees sub-form. How can I get all of the data assigned to each extension to appear on the report?
Tried to upload the file, but it's too large. Can email if requested.
Thanks,
Brian
I have looked through the threads and have not found an answer to my question, so I post it hoping there is an answer as well as documenting useful information for other individuals.
The following code is what I am using to 'pull' data in order to print a certificate. It functions the way I designed it (verified by debug.prints and msgboxes). My question is how do I pass data to a defined report (certificate) based upon the results of a built recordset. When the report opens, the values come up as "#Name?". I'm guessing that the issue is syntax, but I just don't know. Here is the code I have so far (I've even commented it for y'all)...
Looking forward to your comments...
-BT.
Dim RSAgg As Recordset
Dim RSsrc As Recordset
Dim DB As Database
Dim strAgg As String
Dim strSQL As String
Dim strCert As String
'If there is no week number set, drop out
If Not IsNumeric(txtWeekNum) Then
MsgBox "You Must Specify a Week Number.", vbInformation + vbOKOnly, "Required Input"
Exit Sub
End If
' tblAggDesc contains the field names
' that have scores I want to evaluate.
' If someone makes a perfect score,
' then they get a certificate. Fields are
' a1, a2, a3, b1, b2, b3, c1, c2, c3.
strAgg = "SELECT tblAggDesc.AggCourse, tblAggDesc.AggDesc FROM tblAggDesc;"
Set DB = CurrentDb()
Set RSAgg = DB.OpenRecordset(strAgg)
RSAgg.MoveFirst
Do While Not RSAgg.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then 'rounds 2 & 3 contain additional information that is printed on certificate
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & ", tblScores." & RSAgg!AggCourse & "X AS AggCourseX " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
Else
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & " " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
End If
Set RSsrc = DB.OpenRecordset(strSQL, dbOpenDynaset)
If Not (RSsrc.BOF And RSsrc.EOF) Then
RSsrc.MoveFirst
Do While Not RSsrc.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then
strScore = RSsrc!AggCourseX & "X"
Else
strScore = ""
End If
'MsgBox RSsrc!Fname & " " & RSsrc!Lname & " " & RSsrc!WeekNo & " " & RSAgg!AggCourse & " " & strScore & " " & RSAgg!AggDesc
strCert = "Fname='" & RSsrc!Fname & "' AND Lname='" & RSsrc!Lname & "'" & " AND WeekNo='" & RSsrc!WeekNo & "' AND XCount='" & strScore & "' AND AggDesc='" & RSAgg!AggDesc & "'"
'Debug.Print strCert
' this is the point that I have problems.
' I want to pass RSsrc!Fname,
' RSsrc!Lname, RSsrc!WeekNo,
' RSsrc!AggCourseX, RSAgg!AggDesc to
' the report.
DoCmd.OpenReport "rptCleanTarget", acViewPreview, , strCert
RSsrc.MoveNext
Loop
End If
RSsrc.Close
RSAgg.MoveNext
Loop
RSAgg.Close
Set DB = Nothing
Hi guys,
I’m creating a report on which I want to group data base on a field that has two different data one is KEY and OTHER. Now I need to be able to show on the detail section all records base on KEY, and group the rest of the records by OTHER.
Can anyone help me please?
Million of thanks
Hi
I have a report with a column of data. It contains a variable amount of data according to the demand of the user (criteria entered in a parameter query). The problem I have is that the data is short string of 3 letters but there are generaly lots of entries so the report runs over several pages. I would like to be able to creat columns side by side. A bit like with the "Can grow" option" but that a new column appears...
I am not sure I am clear...
Many thanx for your help
I have a database that contained the following fields. VacEarned,VacUsed,VacPlan (Yes,No). In my report, I grouped VacPlan field (which is yes/no field) together and sum it up by VacUsed. However, I'd like to put another formula in my report to subtract the the Sum of the VacPlan from VacEarned...I have such a hard time doing that..Is there way that I can put if statement in the report option that would do the following: If VacPlan is Yes, than Sum VacUsed and use it to subtract from VacEarned..is this possible..
I hope this make sense...
Thanks in advance..
every month i compile a Customer Spend Report for my sales manager.
I currently do this by:
1) Taking my database into table view.
2) Setting the date to Asend.
3) Then copy all jobs from the first day of the month to the last day
eg: June 1st - 29th.
4) Copy the data
5) paste into excel
6) Set auto filter on excel
7) Copy & Paste each customers spend to an individual sheet.
This is very painful! Esp when i know that since all the data i need is in the database and there is a way to get my DB to do this for me.
I have created a query to pull the data out of the database.
Then made a report using the query as the "location of data"
Right i have the report now pulling out the data and showin how i want it.
It shows the groupin of jobs by "Customer Name"
I now want to set a filter to it so that i can tell it to give me the report for all customers for just one month.
EG: June 07.
That way i wil have the data shown as:
Customer:
Month: June07
Cost For Doin Job:
Total Charge Out:
Profit:
How do i do this??? I know it has to be done within the query but i cant work out how to set it.
If any one can help that would b great!!!
thanks in advance!
gary
My employer is using Windows XP Pro and Office 2003 (a few machines have Office 2010, but not mine). Furthermore, the machines are running the Japanese language OS, which has caused some comparability issues with my English XP/Office 2003 at home.
I have a form containing an unbound textbox, with the name MIS. The form's Current event has the following code:
If IsNull([[ResignationDate]) Then
MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
ElseIf [ResignationDate] > Date Then
MIS = DateDiff("m", [NichiiGakkanStart], Date) + Int(Format(Date, "mmdd") < Format([NichiiGakkanStart], "mmdd"))
[Code] .....
The calculates (correctly) the Months in Service of the employee who's information is being viewed.
Now, I am trying to create a report which lists the employees by work locations. The above , and other calculated information, is to be displayed in the report.
I used the wizard to create the report, using data from two different tables (employee & location).
I need to display the calculated information above for every employee at every location.
Example:
"Work Location"
"Employee Number" "Given Name" "Family Name" "Months in Service"
The report, as it is now, displays all work locations and the above employee info except the calculated data.
I've read up on using calculated fields in tables, and I'd prefer not to take this approach.
I posted the following code on a button (report to a first sergeant). I can't pull e-mail addresses from the report (rptLateByUnit).
=========
DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
[Code]......