I'm creating a database that contains some information spread out over multiple tables. In order to enter information into the database there are a couple forms that the user can access to enter all the information, and these data will be placed in the appropriate tables. I am also creating a form that will be used for data-viewing and simple editting purposes. This form will contain data from many different tables and viewed all together in the same form.
I created this second, more complicated form (more complicated because it contains more tables all viewed on the same form) and editting and adding new data doesn't work. It won't let me type in the fields. I've narrowed down the problem to the combination of three tables and I will explain the nature of the relationships of these tables.
First of all, I have a table the contains general person information. These needed to be broken down in to sub-groups, employee and contract. Each of these two sub-groups of people has their own specific fields that need to be entered, so I created three tables. The first table, person, has general person fields. The other two tables, employee and contract, contain fields that are specific to their person type and a foriegn key referencing the associated person information.
My problem is that, on the more complicated form, I wanted to put all the fields related to that entry. So a contract person would have all the fields filled in for the general person, the fields for contract person would be filled in, and then the fields for employee would be blank. Now, I'm sure there is a better way to do this (for example gray out the employee fields when a contract person is viewed, or hide fields for employee etc.) but I'm new at VB and that seemed more complicated.
If anyone has any ideas on what the problem might, or how I could resolve this, that would be amazing. Thanks in advance
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain [IntakeMainID]
tblIncidentDetails [IncidentdeatailsID]
tblPersonnel [PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Hi, I'm currently creating a performance review database for my team and I'm having a few issues.
I was wondering if there is any way other than making a multi table query to have multiple tables linked to a form. The reason for this is because the performance review has 5 sections and I made a table for each section and would like data entered into the form to be entered into those tables.
What I first tried was making a multi table query with all the fields from all 5 tables. Then when I am done updating the values in the form to that 1 table with all the fields I run an append query to populate the data into all the other tables. The issue with this way is it's a manual task and with autonumber on my tables I have to delete all the values from the other tables and then run the append else I have duplicates from when the data was entered last time. Any tips or help would be greatly appreciated.
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I have a query that includes 4 different tables. It only shows info for the Sales Orders that have information in all four tables. Is there a way to get it to show any information available? For instance, if it only has info in two of those tables, it would show that info and the fields for the other two tables would be blank?
Hi there. I am in the process of creating a database without using the wizard and am confused regarding multi table queries. Can someone explain in simple terms why when you create a form from a query, some forms wont let you add data. I think it is to do with the join type but I just dont understand it at all. Say you are creating the order details query, you have to select the product ID from the order details table but the product name from the products table. Why is this? (lol). Please help a confused newbie!! Thanks
I have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table): a b c But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc
I'm not quite sure on how to go about this query.:confused: What I need to know is if a certain patient has had an immunization or not. I need one query to show the patients who have had an immunization and another to show the ones who have not had the immunization.
I'm having a big struggle with adding a multi-value lookup field to a table. I need to provide a long pick list of items (from a source table) but these items must not be output (in forms or reports) in alphabetical order, they need to remain in the order that they are in the source table.
Access wants to alphabetize the output even if a I add a number field to the source table and sort by that.
Is there any way I can persuade access to just give the selected items back in the same order as in the source table ?
Hi, I new to access and I just started using it for a group project in one of my classes. We are supposed to make a database for our "business" so we decided to pick a small independent movie theater.
After some trouble we finally figured out that we had to link the tables with some relation ships. Our basic tables are:
So I made a query that would get the revenue for each product sold in the inventory by multiplying the price and the amount sold.
I was wondering if it would be possible to create a query that would total all the revenue and all the ticket sales and divide them to create a per-person spending ratio.
i have a form with a control for "repair type" and a subform that has a query that searches a table holding vehicle repair records. on the form the user can enter up to 5 repair types, [repair1], [repair2], etc.. Then the control on the form holds each type of repair to search by. Since the user could use one or all five repair fields and put repair types likes brakes in field one on one record and field 5 on another record, how do i search to just pull out those records that have the specified search criteria?
If i put in [form]![control] in each of the 5 criteria in the query it comes back with no records, because it it looking for that type in every repair field rather than once between the five fields?
So I have an event table with a muti value field with all the people on that event. This people field is look up column from my people table (so shows the name but stores the key).Anyhow what I now need to to do is record logs from the event for each person (from that muti value people field). I want ideally be prompted to enter the log data for each person, and show it related to the event it is for.
problem using MS Access 2010. One of my database field's is using multiply values (you can add one than more values in that field by checkbox). So, for example, one record in that ONE field looks like this: "Gastropoda; Mermithidae; Nematoda; Oligochaeta; Scorpiones". When I'm going to "PrivotTable view" I have only "Ga" for instance instead of full names...
I am trying to make a simple database where the data entered in a form will update to a table. My issue is, one of the fields is manufacturing location where I would like the user to be able to enter multiple locations and then have those locations update the table where the record is stored. I've been able to set up a list box with multi-select but am stuck at getting the table to update with the choices made from the list box selection.
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3 AddressID Address Owner
The data is being pulled from two tables:
tblAddress AddressID (pk) HouseNum Street Apt
tblOwner OwnerID (pk) AddressID (fk) LastName FirstName Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners: SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table: SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
I am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments.
The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I am too the point of creating 4 separate databases one for each department.
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables? 2- how to cascade the updates to the related fields? 3- Is there any way to force the data update to other tables?
Hi all, I have a form with three tabs. Problem is that when user opens the form - it navigates to the first tab BUT navigates so far down that user cannot see the tab headings. Although all tabs first item is as close to top as possible. BTW - last two tabs are based on subforms.
I would like to create a form that my users can select multiple check boxes from and have only those records that meet the criteria come up in a search.
See attachment for a look at the form.
I need to be able to let the user have the ability to put in a name and then select any of the check boxes and then click on a search button that will execute a query that will bring up the results.
Any help with code or at least a point in the right direction would be nice.
I have a form "log_file" that I am going to be placing a nework. All I have done is create a new DB, create a table, and create a form. Now I want to make it so users open up the DB file and only see the form and cannot access/edit the table. Basically, It is okay if they go back and forth through the records and change those, but I don't want them to be able to edit the form either.
I'm just not sure where to go from here. If there is more info I should provide please let me know.
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.
create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.
Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form. I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this
