Multiple Source Query
Jun 26, 2006
I'm in the process of learning to use Access and I would appreciate any help that can be provided. The problem that I am facing is that I have data in three different columns, and this cannot be changed. The database that I am creating links to another database to retrieve a table. This table contains two columns of data that are essentially the same for my purposes. I have also created a new table in my database which houses new data and also this will be the location where new records are be stored. Ideally what I'd like to have is a single table with the information from the three sources stored in a single column that updates as the linked table is changed. Is there a way to do this with a query? or should I be looking into another method? Any help is appreciated. Thanks.
Would the append option be appropriate?
View Replies
ADVERTISEMENT
Mar 26, 2005
I am making a master form for three tables which ultimately form a hierarchy. I am getting all the fields from each table onto one form. By default the record source would be made up of an INNER JOIN such as below
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession FROM (PERSON INNER JOIN CONTRIBUTOR ON PERSON.id=CONTRIBUTOR.id) INNER JOIN MUJAHED ON CONTRIBUTOR.id=MUJAHED.id;
I'm trying to change the record source to match using the where clause instead. However the following allows me to view but not add a new record
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession
FROM PERSON, CONTRIBUTOR, MUJAHED
WHERE (PERSON.id = CONTRIBUTOR.id) AND (CONTRIBUTOR.id = MUJAHED.id);
A tip or guideline on how I can correct this will be appreciated.
Regards
View 1 Replies
View Related
Sep 21, 2004
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
Thanks,
JGM
onesweetdude@hotmail.com
View 1 Replies
View Related
Feb 26, 2014
On the form I need to give the user the option to select a 'from date' and 'to date' and for this I have put in 2 calender controls which have the same control source(same table column). The problem I am facing is when I select a date from one calendar, the other calendar control automatically populates with the same date and vice versa.
I need to have a functionality where 2 different date can be selected and then when the Search button is clicked, then data within the date ranges should be displayed.
what changes I need to make to acomplish this ? find a screenshot of the form attached.
View 5 Replies
View Related
Jul 19, 2015
The following code is giving me a "Run-Time error '13' Type mismatch. I have tried isolating both criteria and they seem to be fine but joined together with "AND" they error. Workdate is a Shortdate. Flightnumber and flightID are numbers. FlightID source is a cmb within my form.
Private Sub FlightID_BeforeUpdate(Cancel As Integer)
If DCount("[WorkDate]", "Main_tbl", "[WorkDate]= #" & Me.WorkDate & "#" And "[FlightNumber] =" & Me.FlightID.Column(0)) > 0 Then
Do this....
End If
View 1 Replies
View Related
Jul 15, 2014
I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
View 3 Replies
View Related
Mar 27, 2013
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
View 2 Replies
View Related
Jul 15, 2013
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
View 6 Replies
View Related
Sep 19, 2005
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
View 14 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Feb 13, 2008
Hi!
I have those (simplified) tables that are linked with an n:m relation
persons:
#ID#FullName#
trainings:
#ID#startDate#
training_members:
#ID#trainingID#personID
Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:
SELECT persons.ID, persons.FullName FROM persons WHERE persons.ID Not In (SELECT personID from training_members inner join trainings on training_members.trainingID = trainingsn.ID) ORDER BY persons.FullName;
I also added "me.recalc" to the after_update event of the dropdown list.
It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.
Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...
Thank you in advance!
View 1 Replies
View Related
Aug 11, 2006
Hi
I have o form based on a query that I can search in for last name etc.
And then I have a button for opening a report for the person that displays.
Here is the problem now:
If I have two persons with the same last name I get a report of several pages also showing the person that is not displayed in the form but have the same last name.
What can I do in the query just to get the person only on display showing on the form?
ID is the primary key
Mikael
View 1 Replies
View Related
Jan 2, 2007
Here's the root of all my troubles:
SELECT EmployeeID, NewStoreID As HomeStore
FROM [Store Change] AS SC
WHERE EffectiveDate=(
SELECT Max(EffectiveDate)
FROM [Store Change]
WHERE SC.EmployeeID=EmployeeID
);
This query works perfectly fine by itself. It's supposed to find the most recent store ID for each employee and it does. However, if I use that query inside another query I get the error, "The Microsoft Jet database engine does not recognize 'SC.EmployeeID' as a valid field name or expression." I've tried changing the original query slightly, but there isn't much I can do without ruining my functionality.
View 5 Replies
View Related
Oct 4, 2006
Hi All,
This is a question that is a bit complicated to describe, so I'll try my best.
I have a form with a 'List Box' and i am using 'Table/Query' to fill the box. The table I am using is, "Department;" where it contains a list of all company's departments(Accounting, HR, Payroll, etc.). Now one thing that is NOT listed in the department table is the word "ALL."
So since I have to have this word in the List Box, so users can select "ALL" in the form, I'll have to manually go into the "Department" table to add it in, everytime I need to refresh the table with updated departments.
Is there anything that I can do VBScript code wise or in the "Row Source" in design view that I can fix this issue?
I under some might ask, how hard is it do add this in manually? Please keep in mind the 'department' list box is only one of the 5 tables that I have to refresh at least once a week, so I don't want to have to manually go in everytime to input the word ALL five time.
Thank you.
Joe
View 1 Replies
View Related
Mar 13, 2005
Hiya! So, I have a form with a cascading combo-ish type solution. In actuality it's an option group that helps feed a combo-box. The option group, however, is unbound and the two choices represented in it span several choices elsewhere.
The field I'm reducing with a cascading solution is my [ExpenseCode]field. Each [ExpenseCode] is assigned one of 6 [ExpenseType]'s, numbered 0-6. Deductible expenses range from 1-4, non-deductible expenses cover 5, and 0 is used only by the system in the creation of dummy records and is unnecessary to the data-entry form. Using the [ExpenseType] directly would be confusing to the end user but is important to us during aggregation.
I am attempting to use an option group to simply select "Deductible" or "Non-Deductible" from the listed fields and have my combo-box update itself in kind.
The form is: frmExpenseLogsEntry
The option group is: optExpense1 (where Non-Deductible=1 and Deductible=2)
The combo-box in question is: cboExpense
The table with the combo-source is: lkupExpenseCode
The field I'm populating the combo-box with is: ExpenseCodeID
And the qualifying field is: ExpenseTypeID
The SQL I was using for my control source is as follows:
SELECT lkupExpenseCode.ExpenseCodeID,
FROM lkupExpenseCode
WHERE (((lkupExpenseCode.ExpenseTypeID)=IIf([Forms]![frmExpenseLogEntry]![optExpense1]=1,5,Between 1 and 4)));
but Access keeps changing it and erroring. I think I've figured it out enough to know that this type of statement won't work but I'm a bit stuck for an alternate idea.
Any help would be sincerely appreciated.
~Chad
View 2 Replies
View Related
Sep 9, 2005
Hi,
I have some queries in a list. When I run one of them I would like to show result on form. I made a subform and I defined from code in runtime the actually selected query as the subform's source object. It didn't work. :(
Why? How could I show a query's results on a form?
If somebody could help me , I would be very grateful!
View 2 Replies
View Related
Apr 21, 2005
I’m trying to create a budgeting tool for my company. We operate in many countries and budget at the country level so I’ve set up a one-to-many relationship between my tblCOUNTRY and my tblEXPENSE. My tlbEXPENSE has fields ExpenseID, CountryID, Month, and Value.
In order to create a form where each country manager is able to fill out, and make changes to their budget for the next year I would like to create a crosstab query so they can have a horizontal view of the year broken out by month. The problem I’m running into is that crosstab queries are aggregates so they do not allow data entry.
The only solution I’ve thought of is to create a make table query from the crosstab query, but I still run into several problems. First, I need all twelve months to show on my crosstab even if there is not an expense yet for that month, and once I have it broken out by month how can I transpose it back into a form to be able to append it to my tblEXPENSE?
I don’t know if this is even possible with the way I have set up my tables. I guess first I’m wondering if I should change the setup of my tblEXPENSES so it has a field for each month although this would make it harder to do an annual budget rollup based on country? Or should I make a form in Excel for all the country managers to fill out, compile all the data, and append it to my database? Thanks in advance for any advice.
View 4 Replies
View Related
Dec 19, 2003
I have designed a report that took ages to do the layout etc for.
Now I just want to use that as a template and just alter which query it gets its parameters from.
I can't for the life of me figure this one out
Any help would be very much appreciated
View 5 Replies
View Related
Aug 8, 2007
do you guys use a query as your data source in you form?
if not how do you build a form with mutilple tables with the same unique id.
View 1 Replies
View Related
Nov 25, 2013
I've got the following query as the row source for a listbox on a main form. cboShootName is a combobox on that same form.
Code:
SELECT DISTINCT QrySbfShotList.CamerasFK, tblCameras.CameraNum
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]))
UNION
SELECT null, "(ALL)" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[me]![cboShootName]));
When the form loads, it prompts for a value for me.cboShootName, even though I've given the list box a default value. More important, after the cboShootName is established, ie. in its after update event, if I put
Me.lstCamera.requery (lstCamera is the list box in question), the system prompts me for the value of me!cboShootName. I know the cboShootName is in scope because I see its value with a msgbox diagnostic.
The first column of both the cboShootName and lstCamera refer to numeric fields. Both controls are unbound.
What am I missing? Is there a trick to referencing control values in a query?
View 8 Replies
View Related
Jul 18, 2006
Hi,
I have two Tables TA and TB. I have a set of queries based on TA. I want to substitute TB for TA (i.e. TB will stand in place of TA). I want the old TA queries remain unchanged but now be based on TB.
If I delete TA and then rename TB into TA, I will lose the queries. Therefore I want to 'point' the queries (in the most efficient way) to TB instead of to TA.
Then I can delete TA. Then I can rename TB into TA and the queries will follow along, I expect.
I have been trying in vain to find a way of pointing the queries to TA instead of TB? How do I do it?
I have done it with Forms (Design Views, Properties), where I can select a datasource. But where is the corresponding thing enabling me to select a datasource for Queries?
Thanks.
Adrian
View 13 Replies
View Related
Feb 23, 2005
I have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
Thanks in Advance.
View 1 Replies
View Related
Dec 20, 2004
Hi,
I want to create a delete query that uses information stored in an external excel file. This file is constantly being updated so it isn't ideal to keep loading it into the access database. I need to use the excel file to remove any records appearing in my access table that are matching with the excel file. Make sense? Can anyone out there help - it this at all possible? Thanks,
View 5 Replies
View Related
Aug 17, 2004
I have a Query called Median and all it does is pulls all the fields and all the records from 1 table. Since the Query is connected to a lot of other queries, and the table it's connected to changes twice a month, I was wondering if there was a way to set this up in a form maybe using vb, so a user could maybe Click a new table from a combo box list of current tables in the database, then click a button that says "Change", and it change all the fields to the new table and removes the old table from the query.
Or what might be better is: Already on the form, i have part of the name of the new table already entered by the user (it bases the new name of the new table off this text box). So, what I can use is the Text in that box and an & " the rest of the tablename", and automatically change the table in the Median Query based on that criteria. If that is too complicated, then a combo box will be sufficient.
Another way i guess, would be to setup a hidden field that takes whatever's in my text box (which is a date) and adds " the rest of the tablename", then I could base the replacement table of the Median on that one text box.
Anyway, is there a way to do this?
View 6 Replies
View Related
