New To Access Multi-Table Query Problem
Dec 9, 2005
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:
"Distributors" -relationship-distribributorID-"Ticket Sales"
"Employee Information" -relationship-TitleID- "Position Information"
"Inventory"
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.
View Replies
ADVERTISEMENT
Sep 7, 2007
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.
View 5 Replies
View Related
Apr 10, 2008
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.
Thanks!
View 7 Replies
View Related
Sep 28, 2007
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?
View 5 Replies
View Related
Sep 7, 2006
I have a database... 1 table...40 fields..
I currently have a query set up as a basic a LIKE search for each field and I have to do 40 each time..
Is there a way of being able to do 1 query and have it search all 40 fields?
So my field headings are
Part No
Description
barcode..
Album name
Singer/Group
and so on...
I since some of the albums are compliations I require to search all the fields...
Any ideas?
View 6 Replies
View Related
Jan 4, 2013
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.
View 3 Replies
View Related
Apr 9, 2008
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 have two tables.
Appointments
Date_of_Service
Patient_Name
Chart_ID
Immunizations
Patient_Name
Immunization_Date
Chart_ID
They are joined on Chart_ID.
Any help would be greatly appreciated! :)
View 7 Replies
View Related
Jan 11, 2014
I'm really new to Access and using Office 2007. Comfortable with SQL but have no idea about VBA. How do I get the form to query multi-selections? For example if you download the attachment here and refer to "Form1", let's say the user multiselects BK and McD's for Vendor, burger for Food, and Coke for Beverage. It should be spitting out 3 records but instead spat out an empty table. I tied my last query "query2" with "Form1" to make them work hand-in-hand which filters the master table "Fast Food". Would I need to tweak the syntax seen in the "Criteria:" cells of "query2" to make them read in multiselections?
View 3 Replies
View Related
Dec 7, 2011
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'.
View 3 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Oct 24, 2013
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri
Masonry NSW');'
View 12 Replies
View Related
Sep 5, 2005
Hi
If I want several users to access a database from their network pc's, should I place the access database .mdb file on the server and set the Sharing properties, then install the access application on each of their stations.
They need to each be able to use the database when they need to and read or add data, reports etc.
I am using access 2003.
Thanks
Dave
View 14 Replies
View Related
Apr 19, 2007
Hi guys,
I'm having some issues with a database that I have created. All this time only one user would be in the database. But now I have 2 to 3 people at a time in the database. I get an error and its not letting the users put any information in.
I have it split into a FE and BE. I have it set to share. Any thoughts or suggestions?
Thanks a lot!
View 7 Replies
View Related
May 24, 2006
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.
View 5 Replies
View Related
Jun 2, 2005
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
View 6 Replies
View Related
Aug 20, 2007
hi..
i have used one globle variable ...which gives me a new batch refrence numbers.. but when two users are working same time then ...it gives same number to both of them...i want different number as a result to both of them..
like it will give 37679...to both of them as new batch refrence numbr but i want 37679 and next number 37680 .....
plese help me how i can do this.... i really appreciate your help..
if need more information please let me know...
many thanks...
Nitesha
View 14 Replies
View Related
Nov 23, 2007
Hi everyone,
I just want to have information about the concept of using an Access application with different users at the same time.
Thank you for help,
Nizar
View 2 Replies
View Related
Sep 2, 2004
Hello,
Great forum
I am a virtual assistant and I have a client that wants to "share" their ACCESS database with me so that I can do data entry. How do we go about doing this? Is there a program that allows me to use the same database as my client and their office people? I would be working from a remote location.
Thanks in advance!
View 3 Replies
View Related
Sep 25, 2006
We are planning a database application for a client that will need to be accessed remotely by their consultants. We will initially host this application on one of our servers for a few months, and then the client will take it over and host it. The problem we are facing is that this client wants to use ms access as the database, and doesnt want the interface to be web based. The plan was to use data access pages, and let their consultants use a remote desktop connection to access these pages. I don't use access, but I was under the impression that access couldn't really support multiple users hitting the database in this format. Is this correct?
Thanks,
Greg
View 5 Replies
View Related
Mar 6, 2006
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
Can this be done in a table within Access?
Thank You,
RRA23
View 1 Replies
View Related
Jun 16, 2005
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
View 3 Replies
View Related
Mar 11, 2014
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 ?
View 14 Replies
View Related
Nov 30, 2005
Hello Experts,
Pl. help me in designing a small application..This app. is to be used by 12 users. We need an application to log time spent by the team in office.so this is a time sheet system. I know how to design a table, a form etc..My question is how can many users share the same database?
Let us say that on my computer I have created the database as db1.mdb. (it has all the tables , forms etc) Now for other users to use this what do I have to do?
Also can MS Access be used to make an application which supports simultaneous use by 12 persons?I mean can it support such an app?
Will be most thankful for your inputs..This is urgent.
N.
View 4 Replies
View Related
Aug 17, 2007
Does anybody know whether the forms created in MS Access can support multiple languages? Like... captions can be loaded from a resource file.
If it does can you please share me how it can be achieved.
View 1 Replies
View Related
Jan 8, 2008
Hi
I have an access 2000 database which is networked on a 2003server. I'm having problems with multi user access, as some users can't access the dbase unless it is not being used. Other users on the other hand, have no problem at all.
I'm guessing it has something to do with the machine that is trying to access the db. Although, it is not a permissions issue because as I said earlier, they can get access when no one else is using it.
We are all using XP pro SP2.
Any advice would be great as this is driving me mad!! lol
View 9 Replies
View Related
Sep 10, 2013
I created a database for multiple users, it seems only one user can be in database at a time. Is there a property that allows multiple users to simultaneously use database. My record locks property is no. Is there some other property that needs to be set ??
View 7 Replies
View Related
