Consolidate 3 Queries Down To 1, Possible In My Situation?
Dec 26, 2006
In order to find the most recent piece of data for a particular customer I need to query my table in 3 iterations.
I have a TABLE with data in it pertaining to all customers.
QUERY 1 orders all the records in TABLE by date.
QUERY 2 plucks all records related to specified customer from QUERY 1.
QUERY 3 selects the TOP 1 from QUERY 2.
My main concern is that this all happens automatically. Can I shorten this process at all?
View Replies
ADVERTISEMENT
Aug 29, 2013
I have 3 tables of data, where I want to cross reference information to produce exception reports.
The tables all have Employee number held within them, and these form the basis of my queries.
We want to be able to cross reference locally held staffing information with centrally held HR and payroll records.
The type of information we want to be able to check, is if the hours of the individual are the same in all 3 records, the grade they are being paid for, the cost centre, and that the names match.
Currently we run separate queries for all of these, and I want to be able to consolidate the exceptions in one report, to save looking at the same persons record more than once.
For example, if John Smith has a different name, grade and cost centre are all different - we may be accessing his record 6 times, once for each variance against payroll and HR records.
Is there a way that I can pull all this information together to speed up the reconciliation process?
View 1 Replies
View Related
Jun 11, 2013
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.
I want to combine in this situation
ex. Smith, Peter
Smith, Peter W
I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original NameNew Name
Smith, PeterSmith, Peter
Smith, Peter WSmith, Peter
View 2 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Nov 26, 2005
I am creating a database that tracks reports that are generated from people in the field...The statistics are tracked on a 28 day cycle, beginning on a Monday and ending on a Sunday. Every week the 28 day period loses the first week and gains a week on the end. I need to develop a way to generate the queries for the reports based on a clock that automatically "calculates" the current 28-day period.
View 2 Replies
View Related
Jul 22, 2005
Hello all,
I am trying to make a simple inventory control system. This system will only record input of products and output of products. Explanation: This is a catering service company. I buy tomatoes, rice, oinions, Oils, meat, ect. When we buy this products they usaly come in different packages. The rice can come in 100 pound bag, 50 pound bag or just by the pound. The onions can be bought by the bucket, half bucket, dozen, half dozen ect. The oil can be bought by 100 gallon barril, half barril, Gallon, half gallon, pint ect. The meat by the pound.
I am able to make the a simple invoice and PO application, the only problem is that I want to be able to select what type of packaging the product has and the invoice or PO subforms.
I started with a simple product table, I than created a packaging table and a package detail table. This gave me the chance to create a subform on the product form where I can choose what type of packaging this product uses and also tell it how many units the package has.
So far so good. Now I created a Chef table "this is my Chef to whom I give the product to for cooking" I than created an authorization slip table "this is like an invoice table" and ofcourse a authorization detail table "this is where I pick the products that I give the cook" once I print this out the chef will be able to go to the warehouse and retreive the product.
What I want to do in the detail subform is to be able to pick the product and the type of packaging that I am giving them from a dropdown box then it will automaticaly show only the packaging I assinged to that product and give me the units . This way if I pick the type of packaginh name 100 pound it will put 100 units in the quantity field automaticaly.
So basicaly is an invoice form with its detail but on the detail I am able to pick the product and the packaging that I am giving the chef.
Can you guys guide me on the right direction on how to do thsi?
Thansk
DaniBoy
View 1 Replies
View Related
Feb 7, 2007
Hi. - Please do not lecture me: I inherited this database and it's table designs.
I have multiple tables, each containing a SSN text field and multiple other text fields. There is some overlap in SSNs among tables.
I need to create a report with a subform for each table.
The user would ideally be asked for and enter a specific SSN and a specific string. The result would be the report showing the SSN and each table's field that contains the string.
ie, parameters would be: Specify SSN and for the string would be: Enter TEXT VAUE.
SSNs are simple enough, but the text string entered would need to be, for example: LIKE "2400*"
How do I do this easily so that each text field in each table is searched for that string, without having the user re-enter and re-enter and re-enter... it multiple times?
(I do not know very much VBA, so other ideas?)
Russ
View 3 Replies
View Related
Jan 2, 2008
A friend of mine who is in real estate is looking to learn about Excel. My knowledge is very limited. I can Conditional Format, IF, VLOOKUP etc but I have no idea what can be done with different Sheets in a file.
For example, in one set of data he has it is details on properties for rent. Sheet1, Sheet2 and Sheet3 are for 1, 2 or 3bedroom properties. I assume if a 4 bedroom property is for rent then Sheet4 would be done.
With Excel how easy (or hard) would it be to link data between the sheets. For example, show the properties where the lease is about to end (or whatever) and whereby properties that met the search criteria would comprise 1, 2 and 3 bedroom units and hence be in all 3 sheets.
If what is in 3 sheets was placed in one sheet can Excel do much with that or are you stuck with data that is spread over what appears to be several acres:D
If you can do this in Excel would you need to learn a lot about Excel. To put it another way, this is very easy for anyone here to do what he wants in Access. However, for someone starting from scratch would Excel allow him to get up to speed and functioning easier and quicker than Access
Thanks for any advice
View 14 Replies
View Related
Apr 10, 2008
I have supplier records where the supplier name in each record is different. It may be Supplier ABC, AB,C, ABC!, ABC Inc., or several other variations. In addition each of these suppliers has their own supplier ID. The supplier ID can be one of 5 different versions since the data is from 5 different ERP systems. I am trying to create one table that does not have the supplier listed many times, but has one correct name and one ID that will be able to link back to the original file with all the different names and IDs. I hope this is a good explananation. Any advise?
View 2 Replies
View Related
Jan 17, 2007
Hi i'm after some time saving advice
i'm writing an Access database to quote for windows & doors,
there are many variants like style, width, height, glass types, security specifications and about 4 other options.
what would be the best way of working out how to add the extras for each option,
The price would be based on the style first, then depending on width and height, then on what options were ticked . i.e securtity spec yes/no, then on what glass type, the price gfor that would be based on the entered width x height etc.
is it possible to do a look up, like you can in Excel where it looks at at grid/matrix based on style number and width x height to get the price, but then how would i get it to add the other options which will depend on size and number of openings.
any help greatfully received.....
View 1 Replies
View Related
Feb 12, 2007
Hi. Back again with a badly designed number of tables that I inherited.
Anyway...
I have 20 tables each with an ID field and a dozen-or-so text fields. - (I planned on using a master report with sub-reports, but perhaps I am ahead of myself here.)
I need the user to enter the ID and a value which would only appear in one of the dozen+ fields for that record, in that table.
I set up queries with parameters for the ID and the desired text value.
However, when run, I get the correct client record, but also all of the fields for that record... Not only the field with the desired value.
Is there a 'simple' way to do this knowing ho wmany possible fields froma ll of the tavbles would have to be searched? And not all tables have the same field names!
Russ
View 2 Replies
View Related
Jun 29, 2006
I'm trying to sort a combo box alphabetically except for one value which is always at the top of the list.
The row source of the combo box is a query. record to remain at the top of the list is static- same value and ID number always. I've fiddled around with a few things, but at the end of the day, i'm just not smart enough! (or its not possible -which i doubt)..
any suggestions?
cheers.
View 2 Replies
View Related
Jul 10, 2013
I have a database that, vaguely looks like the following
NAME Value Category A Value Category B Value Category C
Name A -50 0 0
Name A 0 50 0
Name B -100 0 0
Name B 0 75 10
Name B -25 0 0
Name C -100 0 0
Etc. etc.
What I'd like is to find a query that will first, generate a Category D which should be a total of all the values in the three different categories and consolidates the names to one line. So it should spit something like this out.
NAME Value Category A Value Category B Value Category C Total
Name A -50 50 0 0
Name B -125 75 10 -40
Name C -100 0 0 -100
After this, I'd like to have another query that will only display the values that are NOT zero.
NAME Val Cat A Val Cat B Val Cat C Total
Name B -125 75 10 -40
Name C -100 0 0 -100
View 3 Replies
View Related
Jun 11, 2013
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.I want to combine in this situation
ex. Smith, Peter
Smith, Peter W
I do not want to combine in this situation
ex. Smith, Peter M
Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original Name
New Name
Smith, Peter
Smith, Peter
Smith, Peter W
Smith, Peter
View 3 Replies
View Related
Nov 24, 2004
I am currently building a db that will handle information on many events around the world. What I would like to do is set a check box that when people scroll through the different records they could check it and it (the current record they are viewing) will go into a compiled list wherein the iindividual record details of that list which the user created by their choices could be exported to an excell sheet (to automate a reporting need in the job) I have seen functions like this in large websites particularly the classified job sections in the seattle Times . where as you scroll through you see something you like you mark it continue scrolling mark another and then when you are all done you go to a page that has all and only your selections.
Can anyone tell me where to start on this one what am I Looking for Etc.
This will be the next big step in this my first database.
All help greatly appreciated
View 1 Replies
View Related
Apr 9, 2015
I need to consolidate mailing addresses so that many records are grouped to one invoice based on the address they fall under. I am currently using an update query to change addresses that are similar to match exactly. Is there a way to make it so the addresses group together based on similarities rather than exact matches? For example I have two addresses:
1301 W Johnson Street Phoenix, Arizona 85725
1301 West Johnson St Phoenix, AZ 85725-7201
These are the same property however without cleaning them up manually to match, two invoices will be printed. Is there a way to consolidate these?
View 2 Replies
View Related
Apr 10, 2014
I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.
The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.
I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?
But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.
tblCustomers [CustomerNumber] primary key
tblCustomerOrders [OrderNumber] primary key
[CustomerNumber] linking to tblCustomers
So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.
I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.
View 2 Replies
View Related
Oct 15, 2014
I have a table ("Student Info") that looks like this:
ID....Student.....Student #......Teacher Name......Teacher ID
1........A...............432...............Alice.. .................62
2........B...............674...............Alice.. .................62
3........C...............875...............Alice.. .................62
4........D...............934...............Katie.. ................18
5........E...............345...............Katie.. ................18
6........F...............134...............Dan.... .................4
7........G...............734...............Dan.... .................4
The above table suits me well for data entry.For analysis reasons, I want it to have another table or query or something (let's call it "Teacher Info") that looks like this:
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-
Is there any way that I could do this? I have been struggling with this for a while.Just so you know how this data is connected - At the moment, I have a form where I put in new Teacher information. I have a subform attached to that, where I put the student names and numbers. This way, the students are linked with their student #s, and each of them are linked to a teacher. Unfortunately, the table ("New Teacher Registration") that this creates looks like this:
id....Teacher Name
62...... Alice
(+ tab).........A.........432
...................B.........674
...................C.........875
18.......Katie
(+ tab).........D.........934
...................E.........345
4........Dan
(+ tab).........F.........134
...................G.........734
I created the "Student Info" table (above) from this to work with data entry (drop-downs and such). Now I'd like to create a "Teacher Info" table (above) for analysis.
View 11 Replies
View Related
Jan 21, 2014
I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.
View 8 Replies
View Related
Oct 3, 2013
I have two tables in my Access database. Table 1 has three columns, First Name, Last Name and Phone Number. Table 2 has just 2 columns, First Name and Last Name. What I would like to do is write a VBA macro to consolidate Table 2 into Table 1 and insure any duplicate First Name-Last Name combination is removed.
Table1
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Table2
Code:
F.Name L.Name
Albert Black
Larry Miller
John Bush
Table1 Post-Macro
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Albert Black
John Bush
View 1 Replies
View Related
Jan 1, 2014
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions.
The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
View 6 Replies
View Related
Jul 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
Feb 24, 2015
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
[Code] .....
View 9 Replies
View Related
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Jul 16, 2013
I have three Queries and I need to export three queries into one workbook but different worksheet,
Currently I am using ExportWithFormatting , but the result came out is three different workbook .
Is there any way I can export to one workbook ?
View 3 Replies
View Related
