I have a table (Quarter) which acts as a source for another table. When I create a query based on the latter in the Datasheet view the entries of the second column of table Quarter are shown. The PivotTable field however displays the primary key.Where do I need to make the change to only have the second column entries (Q1, Q2, ...) shown in the PivotTable view as well?
View RepliesI was wondering if it is possible to change the primary view for queries from the table view to say, the Pivot Chart view?
View 7 Replies View RelatedA 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 RelatedI have an ordinary table with 20 fields and in the design view the primary field is shown as an auto-number, but it doesn't appear in the datasheet view.
View 4 Replies View RelatedHi!
I figured out that i pivottable in a form would fit my users need.
But, though my users aren´t so brave computerusers I would like to give them a special pivot layout to start with.
At this point, when I open the form, I have to design it from scratch everytime.
I use Access 2002.
How du I do to force a special design?
Jack
I did a search... But couldn't really find what I was looking for. If this question (it seems like a simple one) is answered in some comprehensive FAQ please let me know so I don't waste thread space next time ;)
After creating the Pivot Table for my data, I want to count, in each row, (each column representing Item X) how many cells have a unique row data.
Column - Historical data on Item X w/ each column representing an Item X report
Row - Sum of repair time for Item X
I currently have a total at the end of the row for item X. However, all this tells me is the total time for ALL repair reports for Item X. Were those 10 hours of repair time for just one report of Item X? Or was it for 10 reports of Item X, each taking 1 hour's time?
Thanks ahead of time for the help!
I am doing an outline design of a database system to handle the results from an archery competition in a couple of weeks.
Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name) to enable multiple scores to be recorded against each archer.
For the purposes of the competition only the first score shot each day is valid and so I am using a yes/no box to filter out these results.
What I would like to do is then filter the results such that only the largest of these first session scores is displayed for each archer. (i.e if archer A shoot 578 on day 1 and 585 on day 2 i would like the database to only report the 585, and similarly for the other entries).
I bounced into a big problem with Access 2010 where I cannot seem to be able to remove duplicates from a table containing millions of entries midway through a series of queries.
The table is formed like this:
Date - Serial Number - Reading 1 - Reading 2 - Reading 3
30.7.2013 - 1122334455 - 12345 - 987654 - 654321
30.7.2013 - 1122334466 - 12345 - 987654 - 654321
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334466 - 12344 - 986013 - 453213
As you can see, there are some rows which contain exact duplicates and some are a bit different. I wish to remove those fields with exact duplicates and only the duplicated fields. Running "Find duplicates" in Access gives me about 250,000 rows with such data.
I've tried a few options already:
- I cannot use the date as primary key as there are several serial numbers. I cannot use serial numbers as primary key, because there are several dates. Using reading value as primary isn't an option either.
- Microsoft says I should mark all duplicate values with an x and then make a delete query to get rid of all the x-marked rows. For 250,000 duplicates, that's a bit too much manual 'x-ing'.
- If I do a delete query using the Find Duplicates query as a base, it removes all 250,000 entries from that table, instead of just the 125,000 which had a duplicate elsewhere in the table.
- If I make a query which identifies duplicate data and gives me just one row for each duplicate, the delete query still deletes both entries from the original table.
- I could make a new query which would have only unique values using Totals as criteria (for instance, using First for the Date-column). However, this still leaves the duplicate values in the original table. Note that this database is already 800 MB large and new data is imported once a week, for the next decade or so. I cannot have a table get duplicates every week and leave it there.
- If I make a macro which would create the unique values table first and then deletes the old table, what happens next week when I try to import new readings? I would need to make a new macro each time I try to import new data as the table names change. Or is there a way to first run the unique numbers out, then replace the original table with the new one? With a 800MB database, this would put me dangerously close to the 2GB size limit. I wouldn't be able to use this as a part of a macro, as the database would have to do Compact & Repair each time it deletes the original table, midway through a longer series of queries.
- Having duplicates removed from the original import isn't an option either, as it comes in as a overriding excel sheet for the past 3 months, once a week.
As you can see, it's quite a pickle getting the duplicates out of the original table. This is just a small part of a very long macro, which takes about 15 minutes to complete, but due to duplicates the database is getting way too large.
I have an access table which has a key using letters (A,B,C,D...etc). Against each letter there is multiple rows against each letter. However for testing purposes I only need to get a single row against each key and I was wondering if there is a function I could use which would return only the first record of data for each key?
View 4 Replies View RelatedIs there a way of keeping/setting the primary key when using a make table query?
View 2 Replies View RelatedI have a table which has a primary ID and then date/time then an empid field relating to an empid table,
Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whichever is easiest?
OK, I've tried everything I can think of with this query/subform/records problem. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...
I am trying to create a database which I can use to store client’s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each “order” of an advertising product, including the client’s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).
I have a main form called frmListings, which displays the client’s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.
Now the tricky part…
The subform displays a blank row for a new record, and I would like to be able to enter a new advertising expense in that blank record (date, type of ad, and cost), and have that data added as a new record in the tblAdvertisingExpenses table with a new auto-numbered OrderNumber. I don’t know whether it’s because the subform is actually a dynaset from a query, or because I’ve constructed the query incorrectly (highly likely, I’m still pretty new to queries), but when I add in the details for the new record, I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship (etc, etc).
I suspect that Access is attempting to write the record (along with the ListingID) back to the tblListings table, where the same ListingID already exists (uniquely) as the primary key. But I don’t know for sure, because I don’t really know what the heck I’m doing. What I actually want to do is have the new record I’ve entered in the subform added to the tblAdvertisingExpenses table, which is the record source for the qryListings query which displays records in the subform. It shouldn’t be a problem, should it? So why can’t I get it to work? Perhaps there’s a fundamental flaw in the way I’ve broken up the database into tables, or my assigning of primary keys and foreign keys. Or perhaps there’s a flaw with the way I’ve constructed my query. Or am I forced to have a completely separate form solely for entering data into the tblAdvertisingExpenses table? Or will I need to use a macro or VBA to accomplish what I want?...
Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven’t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
View 2 Replies View RelatedI have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
Code:
TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I've attached a screenshot.
I have a make table query that gets its info from another table that is updated weekly. I would like that when we run the Query Type (Make Table), we would like to add a primary key to a field that exists already starting at 1 every time the query is run.
View 3 Replies View RelatedI need to create a query that strings data horizontally that corresponds to the primary key.
Example of what I am looking for
Fields:
Part_No,
Mfg_No,
Mfg_Name
Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name)
Also if the part_no (primary key) has multiple values I need the data to string horizontally like this:
Part_No, Mfg_Info (Mfg_No1: Mfg_Name1),
Mfg_Info (Mfg_No2: Mfg_Name2).......
I have moved from Access 2003 to 2010. In Access 2003 I was able to create query groups that contained shortcuts to queries in the main database. This worked great for us because we did not want to create user interfaces that required support- but we could have multiple queries in a database and have users look only in their query group.
The database I am using was created in 2003 and had those groups. Is there a way to view, edit and create a similar view in 2010?
This code:
Code:
Private Sub txtPart_LostFocus()
Dim db As Database
Dim strSQL As String
[Code]....
But no entries are added to the table.
I open a query in design mode, paste the first debug above in SQL VIEW and run it:
Code:
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);
And the entry is added in the table.
PartID and SpecID are long integer, Sequence is integer.
I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).
I am using a query as part of a mail merge, there are two forms that use the query, create and view, both forms use the same table. When I click the print button the query runs, and mail merges in a word document.
What I need is a filter on the query which only shows the record currently open on which ever of the forms is open, so the mail merge only happens for the 1 record you want.
I only want to view unchecked boxes in my query. What I can tell you is that I used most of what I could find on google like false/ 0/ Where [fieldname] = False/ and some others but I'm having no luck. This is a yes/no field with checkboxes.
View 4 Replies View RelatedI am designing a database for users with little to no knowledge of Access.
Therefore, I would like to lock/disable the design view for my queries to prevent them from inadvertently making any changes to my query criteria.
Is there a way to accomplish this?
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 RelatedI 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.
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 RelatedI run a query daily and have to alter the date in the design view (as below).
>=#01/04/2013# And <#11/03/2014#
All I want is for the query to run 2 days behind, so for example today is the 12/03/2014, I use the above, and then daily change this every day.
Need sorted Query to be used in a Listbox. The attachment shows a made-up view of the objective.
The first column shows the change in value, then blanks for the first column's next rows, until there is a change of value again.
I do something like this in Excel where the first change in value is bolded. This is to make a list box more readable.