Append Two Queries - Combining Data Into One Datasheet View

Nov 20, 2013

Is it possible to append two queries? The queries have the same layout and labels...it's just a matter of combining the data into one datasheet view.

The two queries are below:

1)
SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], trp.Period, Round(prc.Index*trp.[Variable Value],2) AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.Len, trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel
FROM tblTRP AS trp, tblTRPpercent AS prc
WHERE (((trp.Indication)=prc.Indication) And ((trp.[Media Type])=prc.Type) And ((trp.Geography)="National") And ((trp.Month)=prc.Month));

2)
SELECT prct.DMA AS Geography, tv.Product, tv.Indication, tv.[Variable Name], tv.Period, Round(prct.percentUniverse*tv.[Variable Value],6) AS [Variable Value], tv.Outlet, tv.Daypart, tv.[Program Name], tv.Len, tv.Creative, tv.Campaign, tv.[Campaign Name], tv.[Media Type], tv.Vendor, tv.Channel
FROM tblTVOtherMetrics AS tv, tblDMApercent AS prct
WHERE (((tv.Geography)="National"));

View Replies


ADVERTISEMENT

Queries :: Make All Data Disappear When In Datasheet View

Nov 22, 2013

I have a query that's been working fine for years, however...the powers that be have requested a change. We need to track the lining owners in cars. We've recently gone to SQL on the back end (which I basically know NOTHING about) but I set up the new table, refreshed the connection, etc.

I can see the table fine and it says it has a primary key, auto numbers, etc. however, as you can see from the jpg below when I add the table to the query and link the id as in all the other tables, for some reason, this one makes all the data disappear when in datasheet view.If I delete the table from the query - it all comes back.

View 5 Replies View Related

Queries :: Form With Datasheet View - Trim Data In A Query

May 1, 2013

I am trying to create a form with a datasheet view that will show me all exceptions worked on all member accounts. I have over 10k members that each could have multiple issues (exceptions) worked on their account. I am using MS Access 2007.

Example: I have a member John Smith, with member id of 1234-00 that has multiple "exceptions" worked on his membership, say 5 database records for his account. My initial query I only want to see him listed once. I then would double click his Member ID to open all activity of his account.

I found the code below and modified it to fit my database and it brings me back the results I need for the above. However, if a material change is made on John Smith's account, his number is incremented. His number would be incremented to 1234-01. The next material change 1234-02, etc. I am looking to modify the code below, or another way to do it, that ignores the "-" and any numbers after it. The numbers before the "-" will never change. I searched and saw the TRIM function but couldn't get it to work.

Below is the code I'm using that has the correct names from my database. I have a primary key set up that increments and is tied to the exception, not the member id.

SELECT firstname, lastname, exceptionswrkd.memberid
FROM
exceptionswrkd AS base
INNER JOIN (
SELECT memberid, Min(id) AS which_id
FROM exceptionswrkd
GROUP BY memberid
) AS sub
ON base.id = sub.which_id;

View 3 Replies View Related

Queries :: Return Record In Datasheet View

May 21, 2013

I have a query that returns records in datasheet view. I need a field to accept the user selection from a drop down, then save that to a table. The query is based on a couple queries and one of those queries is based on the table i need to save the user input into. When i make a selection from the drop down list it puts the selection in that field for all the records and then doesn't save.

View 1 Replies View Related

Data From Form Not Showing In Datasheet View In Table

Dec 21, 2004

In a table I have three fields 1. City - which is a combo box linked to a query, 2. State - a text box, 3. postal Code - a text box

When i put them on a form I use the info selected in the City box to populate the info in the State and Postcal Code boxes.

The Control source for the city is SELECT tblPostcodes.ID, tblPostcodes.Pcode, tblPostcodes.Locality, tblPostcodes.State FROM tblPostcodes;

Then on the form i put the control source of the state box as =City.column(1) and Postal code as =City.column(3)

This works fine on the form- and the information is retained - but the datasheet view of the table does not record either the State or the Postcode data that was entered. Is this because I put the control source on the form and the table is not recognising it? if this is the case how do enter it from the table?? really confused...

View 2 Replies View Related

Queries :: Calculate A Value In A Column In A Datasheet View Of A Query?

Oct 20, 2014

tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?

View 2 Replies View Related

Queries :: Datasheet View - Expr1003 Appears Instead Of Field Name

Aug 1, 2014

I have windows 7 access 2013, I had a query made, and wanted to add fields to it, so i went into the SQL and added several fields. Only the first one i entered didn't work and on the query is called "Expr1003" when in datasheet view. Its right in layout view, and even when i tried adding the same field in layout view, it made it "Expr1004". spelling is correct, in both source table and query, i don't know why this field doesn't work especially when the others did.

View 14 Replies View Related

Tables :: Access 2007 / Data Not Visible In Table Datasheet View?

Dec 10, 2012

I have recently been modifying an existing Access 2007 database, everything seems to be working correctly, except that the main table holding most of the data is acting strangely.

If opened in database view, it is completely blank - no column headers, no data. Record count is correct and I can step through/select records, but there's just nothing visible at all.

The data clearly still exists as it can still be viewed in forms / queries / reports.

Only thing I can think of is that I have deleted a bunch of outdated queries & reports and it's possible that one or more of them may be cross-referenced with the table in another query, but still can't see why that should be causing this problem.

View 2 Replies View Related

Tables :: Datasheet View Of Table Inside Subform Not Showing Sorted Data

May 25, 2015

I have a linked table(tblxyz) having property set as ORDER BY ID DESC, ID is autonumber, so my table view gives me latest record on top.

Now I have a subform , where i am calling this Table.....

[Forms]![MainForm]![Sub_DisplayFm].SourceObject = "Table.tblxyz"

This gives me datasheet view of the table inside subform but its not showing sorted data

Interesting thing is it give sorted data view when my table is not linked and is in same access file.

View 4 Replies View Related

Queries :: Multivalue Field - Values Revert To Primary Key In Datasheet View When Saved

Dec 23, 2013

A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values

View 3 Replies View Related

How Do I Show The Subform As Form View Instead Of Datasheet View :confused:

Jul 19, 2007

See title :)

I have seen that in some sample db's rightclicking the mouse showed the menu with an option to check or uncheck the dataview option, unchecked the formview was applied, but when i 'just' import the subform, i cannot find somewhere the possibility to change from dataview to formview.

Maybe someone can help me with this, probably very simple(?), question?

View 4 Replies View Related

Forms :: From Pivot View To Filtered Datasheet View

Nov 14, 2013

I want to "zoom" to the underliyng data from pivot view. When in excel someone doubleclicks a field in a pivot table, it automatically creates a table containing all the lines that field were made up from. I want to achieve the same behaviour in Access.I started to think towards a VBA coda, that could be initiated from the form's double click event. It should go to datasheet view with the prpoer filter criterias.

View 1 Replies View Related

Queries :: Combining Data Results From Multiple Queries

Sep 30, 2014

I am working on a report that has some special characteristics.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is : (complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.

View 4 Replies View Related

I Want Datasheet View... Why Does It Keep Showing Form View?

Apr 6, 2006

I have a form which is opened with a filter from another form. For some reason it views the form in form view, although it was set up as datasheet view. I even went to the form properties and the default was datasheet and i made it not to allow other views. This still didnt solve it. I want it to be in datasheet view because i want to show multiple records at the same time.

If u kno how i could resolve this please offer ur help

thanks

View 6 Replies View Related

Queries :: Combining Data In Access Query Using Builder Function

May 13, 2014

I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:

NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed

I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:

NameColour
CarBlue;Green;Yellow

How i would do this.

View 5 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

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

Datasheet View

Nov 3, 2006

when you select the Data sheet View you display error messages in the Accounts Sub reports fields.

The database has been included as a reference:
Forms- Double click Accounts Form By Discipline -data sheet view

Under Current balance,Spent, and Encumbered Columns you get error message instead of the actuall totals.

Please tell me how to correct this prolsdms

View 1 Replies View Related

Datasheet View

Aug 20, 2004

Is it possible to switch the view of a subform to datasheet when a command button on the mainform is selected?

I know it is something involving acFormDS...but I cant figure out the rest of the code! I appreciate your help in advance!

(P.S. I know you can right click the form and select datasheet. Unfortunately there are not many people here that are familiar with MS Access and I dont trust that they will know this)

View 1 Replies View Related

Datasheet View

Jun 18, 2007

I am reviewing a program that in datasheet view has '+' signs in the left-hand column -- it displays rows of companies. When I select a '+' another 'sub-datasheet view' of employees of that company appears, partially covering the original datasheet view of companies.
Is this the way sub-forms look in datasheet view or is something else involved?
Thanks

View 4 Replies View Related

Queries :: Blank Returns - Datasheet With Table Headings But No Data Contents

Jun 2, 2014

I'm relatively new to access. I have a database i created for my school project everything works fine until I try running a query with 2 or more tables, the queries return a datasheet with all the table headings but no data content, I ensured that there is data in the tables that I'm trying to join for the query.

View 6 Replies View Related

Datasheet View In Subform

Jul 17, 2006

Hi. I am trying to create subform in datasheet view with one column (total 3)as drop down list. Is this doable? Thanks.

View 3 Replies View Related

Querying A Datasheet View

Apr 25, 2007

Is it possible to do a query of the datasheet view of a subform? If so, how does one go about it.

Thanks.
eileen

View 6 Replies View Related

OnLoad View As Datasheet

Mar 9, 2005

hi

i have created subforms and set the Default View to Datasheet
Now i have a form where i am adding command buttons on it to load those subforms but They are not Loading it as DATASHEET VIEW.
Any Clue?

Thanks

View 5 Replies View Related

Subform Datasheet View

Jan 5, 2006

I have a subform that is in databsheet view. When the user logs in to the db it sets the global variable with the group level they have.

I would like a column to be hidden for a certain group of users and have tried the following.

On the subform opening event i have

if vlevel = "engineer" then

me.Hours.columnhidden = True

Else

me.Hours.columnhidden = False

End If

And i have done the same on the Main Form open event identifying the subform by Form!Subform![Hours].columnhidden = True

But neither worked.

Can any suggest something.

View 4 Replies View Related

Datasheet View In A Subform

Jul 25, 2006

Can anyone tell me how to manipulate a single cell in a datasheet view, or if it is even possible?

View 3 Replies View Related

Mimick Datasheet View

Mar 10, 2006

I have two Access files. One is just forms which I use as a front end app. The other is the database that I store all my tables/data/etc...

I want to run a query to display data from these tables and display it in like a datasheet view. I know how to do this if it was all the same Access file, but not how to do it with two separate files.

Thanks,

JMH

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved