Hi I'm in need of some help it's been many years since I've worked with Access and I'm struggling a little.
I have a database I am making that logs customer airport drop off and pick ups and I want to produce a report that merges the Drop off date and the pick up date into one column and I can't seem to get access to do this.
Currently I have 3 tables
Main Info table with the following records
Booking ID(Linked Primary Key) | Customer Details |
Outgoing Table
Booking ID(Linked Primary Key) | Outgoing Flight Date | Outgoing Flight Time | Outgoing Flight Number
Inbound Table
Booking ID(Linked Primary Key) | Inbound Flight Date | Inbound Flight Time | Inbound Flight Number
So I want a report that will get both the outgoing flight dates and inbound flight dates and put them in one column so I can create a report that tells me what pick ups are needed on each date.
I have a database that keeps track of street light inspections.
There is a form with inspection info and a subform with any faults found - Works Fine
There is a report with the same information - Works Fine'ish
The report allways used to work fine but for no apparent reason it previews fine but when printing I get the following message and the subreport information does not print (although if I open the sub report seperately it prints fine).
'This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.'
I am banging my head against the wall with this one. It used to print fine and I have made no changes, apart from data. Have tried compact/repair to no avail.
Could someone please point me in the right direction, my head is hurting...
I'm getting seriously frustrated with Access now so I hope some of you can help me out.
I have made an access application to enter various types of products (eg paint, tools, glue etc). The organization I have built this for has asked me to create a report which can be used as a catalogue of all their products.
They want this report to keep different categories of products in different sections. So say the paints will go into a section with an orange background and the tools have a blue background. I thought I could just let it print a number of reports (one for each type of product) however this will give problems with the page numbering and indexes etc.
Anyone have any ideas how I can let the lay-out (mainly colors) change if a new category kicks in and also force this category to start on a new page? Any help is very much appreciated!
I have a table called 'students' with the appropriate fields. There is another table named parents_prof which contains records of common professions. The tables are related through the 'ID_parents_prof' of the 'parents_prof' table to two fields on the table 'students', thus 'students.father_prof' and 'students.mother_prof'.
1st question) Is that possible? Is it possible to create a relation between the key field of one table to two different fields of another table? Is it a good practice?
I did this because I want to build a query which checks for both fields together, meaning I want Access to return a record if the appropriate profession is in either field of mother's or father's profession.
FROM parents_prof INNER JOIN students ON (parents_prof.ID_parents_prof = students.mother_prof) OR (parents_prof.ID_parents_prof = students.father_prof)
GROUP BY students.surname, students.name, students.father_prof, students.mother_prof, parents_prof.parents_prof
HAVING (((parents_prof.parents_prof) Like "*" & [enter profession] & "*"));
Access cannot represent the above graphical, so I had to enter the
OR (parents_prof.ID_parents_prof = students.father_prof)
in the SQL formula.
2nd question) Is the above proper query, according to the rules?
I want to track inventory. All our items are tubes that are stored in boxes in a 10x10 grid. So I have a single access table with columns for tube ID, box ID, and position in the box (numbered 1-100). That works fine in terms of 'where can I find tube x'. However it's also useful to be able to look at the physical box & check that there are tubes where should be tubes, and empty spaces where there should be empty spaces. So for each box in the database, I want to print out a 10x10 grid, with the appropriate tube ID's in the appropriate place. Then I can easily check the layout in the database against the layout in the actual box.
I have created a report with a subreport for my database. The user selects the project for which he/she wants to see a report. Once the project is selected, the report is displayed in a popup window and maximized in the print preview layout. This allows the user to view and read the report. Once this is done, there are no buttons or menus on the screen that allows the user to send the report to a printer or file.
Other than the report, there are minimize / maximize and close buttons at the top right of the window and page selection buttons at the bottom left of the window. If the user wants to print the report, they must either hit ctrl-P or right click the mouse on the screen to display a menu from which the user can select print to open a print dialog box. Is there any way to add a button or menu to the print preview that appears on the screen to make printing easier?
I work for a company that maps schools and school information and data for a state project. I have put together a DB to enter and track all of this information. Currently, I am trying to produce a report that will group by County, then show School Districts w/in the County, and then show Schools w/in the District. On the County Group header, I would like to show the Count of Unique/Distinct School Districts w/in the County and show the Count of Schools w/in the County.
The problem I am having is that I have not been able to get the count of distinct Districts w/in the County. I am able to get the correct count for the number of Schools w/in the County and Districts. I would like the District count to show in the RED boxes on the form.
Im new to access and i have a problem!! I have a report that i need to be sent out monthly to 3 people. The problem is i dont know where to start. I have been looking through the forum, but nothing that i can get my head around, so there anyone out there who can help???????
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
I have a sub report on my form to show all data needed for a delivery note.
these fields are :
House Type Plot No Product Delivery Note No
when i use my query to search orders in my subreport - it will show the required data but will also show data i didnt search for ? why is this?
Sometimes my search results in the subreport show further down the subreport than the irrelevant data - all i would like is for it to show the specific data not the irrellevant data.
I have created a report that prints a transaction input via a form. All the data has been posted to tables while the document details are still on the form. The source for the report is a query that gets its "Document ID" from the current form as its CRITERIA. This works fine. I click a button and the report prints.
Now I've added a datasheet that lists all the "Document IDs" that have been posted within a given date range. I've added a Macro to open(reprint) the same report when any Document ID is double-clicked. This is working except that it prompts for a Parameter Value and references Forms!DocDataEntry!txtDocumentID. (Note: this is the name of the original data entry form which is no longer open)
If I manually type the Document ID (that I just double-clicked) in the parameter box, the report prints correctly. But this shouldn't be necessary.
I know I'm missing a WHERE clause on the Macro that opens the report but nothing I've input works. I can't even hard code a document number. Actually any Where clause provided prompts additional parameter boxes to open and they ALL require the SAME INFORMATION... the Document ID.
I'm thinking that the Criteria on the Document ID in the query should be changed to allow a Document ID from any active source.
I have subforms that are datasheet view, if the user rearranges the coloumns, or resizes etc , I would like to save what they have done so it is as they left it next time they open up the screen.
This works fine with an mdb (all my forms are set to close acsaveNo).
However in an mde this doesn't work.
Can I get this to happen in and mde.
(A button that saves a layout change would be fine for my purpose)
I'm pulling my hair off with this situation. I have an excel file that was given to us by the payroll department of our company, but my manager doesn't like the layout in which was given to us. I've attached a sample of the file, the file has two sheets the first one is how the original file looks like and the second sheet "final look" is how my manager want the file to look.The original file is pretty big, it's for 33 different employees and it contains 93,500 rows. I think it will take forever to do this manually, any ideas would be really appreciated.
I had a couple of forms go corrupt in one of my databases this morning, which has around 50 or so tables in it. I repaired the database by creating a new db then importing all the db objects from the original and the 2 corrupted forms from yesterday's backup.
However, the relationships table is now a disorganized mess. Is there some way to import the layout of the relationships table from the original db or am I stuck with manually re-arranging?
I have an issue with a database layout and I am not sure which is the best way to proceed.
The problem is in an invoice part of a db . When a service is delivered, say 2 hours of a technicians time, it has to be assigned to a particular tech. No problem when there is only one tech however there could be two or more techs who have helped to supply this particular service and the time ( and income) needs to be divided up between them. However the invoice can only show one item the ie total time taken to deliver the service ( quite likely to be a fixed cost for a particular service ).
Possibilties I have considered are: to have a number of fields in the invoice detail table , one for each tech, however I would have to assume a maximum number of possible techs per job and also there would be a lot of redundant fields and trying to analyse how many hours each has worked would be painful.
Each tech could be assigned a code ( single number or letter ) and these could be stored in a single field . The code would then have to be manipulated to work out how many techs worked on each item and how much each had worked. Again this is rather messy.
I think the ideal would be for each tech to have their own table where each time an service was invoiced which they helped delivered the appropiate percentage is assigned but I cant quite see how to do this.
I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time. I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.
My only problem is that I have quite a lot of fields...
So I Decided to Create 4 Tables In each table I am using the same ID for a person so should be a 1 to 1 Relationship.
1 General Information 2 Info Relating to Skill 3 Work History 4 Qualifications.
I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed
But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields. Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables. Is it better to have all the records horizontally, or as a one to many with duplicates.
Please find attached a copy of the tables. I would really appreciate any advice Regards
I created a sub-form using the wizard; however, I can't get it to view as Calumnar even when I move its text boxes about and change their order. When I run it, it always show as a datasheet!
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...
The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.
Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.
Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,
Here is where I stand currently with this project…
I have 4 tables, 1. Business (it includes basic contact info that is common to all business) 2. Category (is basically a list of all possible categories) 3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value) 4. BusinessType (a list of business types that a business can belong to)
This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.
It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
I have designed a few databases for my work. I have been recently assigned a new one that has me a little stumped. I am looking for some suggestions as to how to go about this. Essentially, we have a training department that does training in the various aspects of the job. In the various classes, the "students" take a midterm exam to gauge the level of retention of the training midpoint in the class. Then at the end of the class, they need to take a final exam. My department director would like the "students" to be able to take the exams using Access, since 99% of the questions are either multiple choice, yes/no, true/false, or fill in the blank, and Access would be able to "auto-grade" the tests, essentially. I am at a loss, basically, as to the layout. The "student" information table would be the easy part. I am unsure of how to create the tables that would have the questions, depending on the class and whether it is a midterm or final, with the answer choices attached. Any suggestions?
Ive been using the form wizard, with columnar but I find it squishes up some of the words, and doesnt use the whole screen width, is there a way I can change this? and is there anywhere that you can download more styles?
ok, i have a few design requirements and am unsure as the best way to approach the situation..
here is the basic layout of my tables..
http://www.londonheathrowcars.com/bait1.jpg
i will basically need a form with the following layout
http://www.londonheathrowcars.com/bait.jpg
you enter the date at the top of the form.. and then all the records you enter will use that value as the job date..
also.. the customer name and number will need to be entered in this form.. and the customer id will have to be registered to the job in the job table..
can anyone suggest where i should start with regards to creating a subform.. a query.. etc??
