This works just fine in creating the desired result - EXCEPT I don't get all the records.
When I remove the 'cartesian table', and right join everything, then I get the correct results. If I keep everything as-is and reintroduce the cartesian table, then I get an error about there being an ambiguous outer join.
I have an Access based CRM system that was built for me in 1998. Amazingly it is still pretty effective. However, I would like to make a small adjustment in the programming..
Once we've added the details for an order we press continue and the screen closes and one copy of the acknowledgement of order form. I simply want it to print three copies!
I believe this is the coding part of the command that is effected.
Rem Print Report DoCmd.OpenReport "Order Acknowledgement" Rem Close Form DoCmd.Close acForm, "Booking Entry"
Our access database keep track of children attending an after-school music programme.
Each week we print registers and give them to the class teachers for them to mark who is coming. The registers are produced as a report, grouped by School then by Class.
We run 3 times a week so each week I need to print off 3 copies of the registers for each class.
Is there a way to print multiple copies of each group in a report? This would save me quite a bit of time each week.
I am looking to set up a database where I have a table of records that correspond labels to product numbers. The product numbers may have anywhere from 1 to 30 labels that correspond to them, and I want the user to have a form where they search by a product number and then the labels that correspond to that product number are printed out on an Avery label.
Here is how the main data table will look: (everything is simply arbitrary to illustrate)
For example: If I type in "1111" into the form - the labels that print would be AA, AB, AC, AD, & AE
I know how to set up labels to print using forms, but the only way I can think of this working is by looking up the product number in the table, then copying the row of labels into another table (let's call it "labels to print") and pasting them in a column, then printing to the labels, and clearing the "labels to print" table for the next time.
Is this the correct route to go? If so, how do I go about looking up the product number in the main table and copy them to the "labels to print table".
I have a table that contains a lot of different information regarding to mailing.
There are columns for Name, Address, Address2, Address 3, City, State, Zip, Country, Base Name, etc.
Some units will only required parts of the above information. I'm using the Name as an identifier in the DB only and Address is the beginning of content that I want to use.
My issue stems out of the fact that the City, State, and Country are not always required but the Base Name, may be used in lieu of the City.
I was looking at making checkboxes next to each of the columns on the form on the field and then using those checkboxes somehow to create a report for Mailing Labels.
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
I have two tables which are in a one-to-many relationship (the example I am dealing with is a mother and her children).
I want one form for the mother, with fields such as date of birth, and when you click Add Record I want the mother table to be updated with the new record.
However on this form I also want a field for "number of children" and when the Add Record button is clicked, not only does the table containing all the mothers get a record added, I would like XX records added to the children table where XX is the number of children entered into the form. These should have separate IDs but retain a common field that identifies the mother.
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.
My table is in the form, TimeandDate,WS127m_Avg,WS82m_Avg....
I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s FROM CL_AllData WHERE (((WS127m_Avg)=9999)) GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);
I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).
I worked yesterday to create a database consisting of a table, multiple querries and a form. Today I went to open it and I got a different database I worked on months ago. The properties say it was created yesterday, but it is not the same database and I can not find the database I created yesterday.
Hello everyone! I'm trying to print a single label to a Zebra printer, but I have ran into a small problem. I'm using a checkbox to select the records and a query to catch the record I want printed as a label. I print the label and deselect the record I printed and all works fine. I print the next record and the previous record prints on top the current record. If I turn off the printer in between printing labels all works fine. Any help would be a great because I going to wear out the on/off button on my printer
I've created labels in my database and when I print them on a label printer, I get a blank label in between my printed labels. I've checked the printer settings and properties and can't find anything. I've also checked the database properties and can't find anything. Is there something in the database that I'm missing?
I currently have a set of labels that works flawlessly - it has 2 columns and 20 rows. I have a form that I can select which label to start printing on so I use up a full sheet with no waste. [working labels attachment]
The labels I'm trying to print on now are 1.25" x 10", as seen HERE. Since I cannot rotate the whole report 90 degrees, I set up the labels to have one column with six rows instead of 6 columns with one row (landscape instead of portrait).
I copied the entire label printing process from the known working report. For my new set of labels, the label prints great if I have just one record, and/or I start at label #1. If I want to start at label #6, it just prints slightly lower on the page instead of at label #6 position (which is the bottom of the page). [label at #6 position attachment].
I have a small receiving inspection database that prints lables for each item received. However when they use the program on a different computer the lables do not print on the lable printer at that location.
I have a report that should print on labels ready to be stick on a book, but how can I tell the report to start from 3th row 2nd column so I do not waste any labels?
I have a non standard size report (for printing labels)
The report is just over 10cm wide and just over 15cm deep.
I have solved the blank pages caused by exceeding the width but I'm still getting a blank page when I move some data down. I am still within 15 cm but it's throwing me a blank page. Even if I extend the in design mode depth to say 20cm (ie the height in Detail) it's throwing a blank page (I can see this in print preview).
I initially set up the report (it's a long time ago so memory fades) using the label wizard setting up a label 4*6 inches (10.16 * 15.24 cms) and it is this i'm trying to tweak. Is there a property I'm missing somewhere?
I have to print address labels. 10 x 3.8 cm labels 2 per row 14 per page on an A4 paper. Pretty straightforward one should say. But no. I play around with the margins, column heights and widths, row and column spacing page size wasting time. Is there a hidden and not documented relationship between those elements? For example how is the column height and row spacing linked together? How is this normally done in a professional way?
I finished a db for a client to help her keep track of the classes she teaches, her students and other related info. The db has several tables and one main form with subforms. I read that it was a good rule of thumb to design the fewest posible forms to make the navigation centrilized and so I did. (I also spent some time on making it look less Access like, more of a stand alone app and wish to preserve it) Up to this point the client was using Outlook to store all the contact info plus anything else she could jam into various "notes" fields. Now she wishes to have some simillar futures in the db to the ones outlook offered, one of them: being able to open multiple contacts (records) in new windows. So basically she wants to open many instances of the main form so she can jump between the records without closing the previous one. My question is: what is the nicest (cleanest from the point of db design) way of giving her such functionality? I thought about giving her an option to open the new record (student) in a tab, but I'm not sure how to acomplish that. Another way would be to copy the main form several times and open those as she clicks on "open in new window", but I don't think it's a good solution, becsue: e.g. How many copies do I create? I'd have to go over all the vba in each copy and adjust it so it works properly with the copy, plus all the vba in each subform... :eek: Any thoughts would be greatly appreciated on how to tackle this. Also what are the consequences of having several instances of the same form open (editing same record by mistake, etc..)
In Access 2007 is it possible to alter a macro so I can print 2 copies of a report. I have created a simple macro which opens up a report based on a value in a data entry form. I want to automatically print 2 copies of the report. Is this possible....
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate 1 John Doe 05/06/2014 06/27/2014 2 Jane Doe 04/24/2014 05/15/2014 3 Steven Smith 05/15/2014 NULL/Empty 4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden) John Doe 5/19/14-5/25/14 Checkbox Null 1 John Doe 5/26/14-6/1/14 Checkbox Null 1 John Doe 6/2/14-6/8/14 Checkbox Null 1 John Doe 6/9/14-6/15/14 Checkbox Null 1 John Doe 6/16/14-6/22/14 Checkbox Null 1 John Doe 6/23/14-6/29/14 Checkbox Null 1 Jane Doe 4/28/14-5/4/14 Checkbox Null 2 and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
1Create a report called rptCustomersByGender. The report should:
a.Print your company's name at the top of the first page only b.Print your data labels at the top of each page c.Group your data by gender d.Display all the customer information in the detail section e.The customers should display in ascending numerical order by Last Name f.Below the list of Female customers, it should say: "Total number of Female Customers:" and then the total number of female customers should display. Do the same for the male customers g.The page number should display at the bottom of every page h.The list of Female and Male customers should each print starting on a separate page. i.Make sure the report runs without producing any errors and looks professional.
I created a report and grouped it by gender but I can't do step F. creating two different labels for each group..
I have two controls on my form that I want to reference in an append query to create a new record. The first is a text box, the second is a combobox that is populated with all of the dates associated with the text box. The selection of a date updates a subform based off of a hidden column (chart ID, an autonumber field based on combinations of record numbers and visit dates) in the same combobox. I would like the user to be able to enter a new date and create a new record that would contain the next autonumber in association with the new date and the record number from the text box control. I have tried using an append query with the following SQL, but I consistently get errors referring to "type mismatch." Any help would be greatly appreciated; thanks in advance.
Code:Private Sub cboDOVSearchChart_NotInList(NewData As String, Response AsInteger)Dim ans As Variantans = MsgBox("The date you entered was not found. Do you want to add a newdate?", _vbYesNo, "Add New Date?")If ans = vbNo ThenResponse = acDataErrContinueMe.cboDOVSearchChart = NullDoCmd.GoToControl "cboDOVSearchChart"GoTo exit_itEnd If' add dateIf ans = vbYes Then Dim strSQL As String strSQL = "INSERT INTO Patient_Visits ([Medical Record Number], " _ & "[Date of Visit]) " _ & "Values (" & Me.[txtChartMR] & ", " _ & "#" & CDate(NewData) & "#)" 'this line has the arrow on debug CurrentDb.Execute strSQL Me.cboDOVSearchChart.Requery Me.cboDOVSearchChart = NewData Call cboDOVSearchChart_AfterUpdateEnd Ifexit_it:End Sub
I'm pretty new to making databases outside of a basic access class..Is it possible to make a record in one table that makes a new record in 5 different tables using different bits of the initial record?I want to use the data entered in an evaluation form to create a new entry with the basic identifying information in 4 different tables.