I am attaching a pic of my relationships page to show the table structure I am dealing with. What I need to do is match forecasted values with actuals based on the same date range, sales_num and unit.
The two tables that hold this data after downloading from our systems are
ForecastOE_T
ActualOE_T
The other tables are crossrefs for additional information and aggregation.
I have been able to query successfully each of these separately based on my desired date range, and successfully aggregate based on salesrep number and unit... but, I don't know how to properly write the query joining them.
Here are my issues:
Not all reps forecast all units.
Not all reps sell all units.
So, when I try to join these values (forecast and actual), the query I write gives me only those rep/unit combinations that BOTH a forecast was made and an actual appears (we don't get a download of zero activity). What I need is output that shows the rep/unit combination for all entries that have EITHER the forecast or the actual, with zeroes defaulted wherever appropriate. This has been done in excel in the past, but the file sizes are prohibitive now... and I'm trying to help out, but am stumped here.
Say you have multiple excel sheets where forecasting is done daily.
For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10
and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)...
How to I join all these forecasts into one table; vertically with each forecast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates...
Probably an easy one...I have two tables, each with a number field say "Cat-ID" and "Ref-ID". The query I am looking to write will show the outstanding values. For example
I have two tables of software names. A count is needed of those names that are in both tables, as follows :
Table1.software name Count1 Table2.software name Count2
matching on software name.
The problem is that names match only partially, for examle:
Table 1 :
Acrobat Writer 6.0 std
Table 2:
Adobe Acrobat standard edition 6.0
This is the same software, but how do I make Access recognize that? can't enter every parameter manually using wildcards, because 1) tables are too large to ask for each name separately 2) Sofware names matches on different parts, not only a first or second word.
how can i make query that selects the first match of a criteria, or multiple criteria. lets say list of members first record in CA only then first record in OR only .... basically select first record from list of criteria.
I have a table with two fields: Field1 Field2 S11.1 111001 S13 130001 S11 110001 S13.1 120001 I need to query to find where Field2's first three digits does not match the numbers in field1. Giving the sample: 120001 would be listed in the results because it does not match the numbers in S13.1 130001(2nd record) does because if there is no "."; it defaults to zero. I don't know to set up the criteria or what to ask so it would give me the results needed. Can anyone help please. Thanks
I am exporting an Oracle report to excel, and using an Access macro to import into a table. The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later). This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate. Any help would be appreciated.
I have converted an Old FileMaker Pro DB to Access. There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years. One of the main fields we query by is the "Description" field. If I have the following Criteria: [Enter Document Description] Then save and exucute the query Access seems to look for an exact match to the value I provide. I need a query to return ANY record with the criteria value in it. For example If I entered "Fire" I would like to return anything with "Fire" in the description: B Building FIRE alarm Install Building C FIRE sprinkler Demolition A Building FIRE Exit Signage Site FIRE Protection Main etc. How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?
I realize this is a dumb, rookie qiestion. I tried Access Help and I tried a "search" in this Forum, I probably didn't do a good job describing my problem.
How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's This shows my query at present.
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
Both tables have a SSN field with the following format:
000-00-0000XX (xx stand for two additional characters)
I have a search form with a textbox that I use to type criteria to open a form. The text box has the following SSN input mask: 000-00-0000CC;0;_ This allows me to return data even if I have a part of the SSN
I have now placed a button which verifies if the SSN I will type in the textbox already exists in table History. I am having problems with the SSN format as I have to type the whole SSN while I would like to type part of it (like the textbox input mask).
If Not IsNull(DLookup("[SSN]", "History", "[SSN] = '" & Me![txtbox] & "'")) Then
I need help writing an SQL statement in Access 2007 to select the closest date/time. I have Spot Time (the date/time commercials ran) and Call Time (the date/time we received calls).
I need to match these two fields so that I can tell which calls came in within 5 minutes before the Spot Time and which came in 15 minutes after.
Between DateAdd("n",-5,[SpotTime]) AND DateAdd("n",15,[SpotTime])
This gives me results within that 20 minute time range, but does not match each call up to the closest time.
For example,
SpotTime--------------- CallTime 6/30/2007 10:45 AM 6/30/2007 10:55 AM 6/30/2007 10:50 AM 6/30/2007 10:55 AM 6/30/2007 10:55 AM 6/30/2007 10:55 AM
In this example I would need the three Call Times to correspond with the 10:55 AM spot because the Call Time occurs very close to the Spot Time. I am unclear how to proceed.
I have got a combo box with a product code in it, and depending on which code is selected I want the data to come from one of two different tables. Is there any way I can do something like a match formula in Excel to see if the stock code is contained in a table?
I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)
My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.
I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.
Greetings all! I am working on something and it has me stumped. Basically I have a list of Eastings and Northings and I am trying to find the closest postcode centroid based on the PAF file (for those that do not know of the PAF file, it contains a list of postcodes and the easting and northing of the centroid). The best way that I can see of doing this is: For each record I am trying to match, calculate the distance from the Easting,Northing to every easting,northing in the PAF. Select Min(Straight Line Distance) from the results.
However I do not know how to do this without matching each record that I am looking at individually! Can anyone give me an idea of how I can put this query together? I am using MS Access :(
I have 2 tables in access. One is a table with the us state abbreviations.
I have another table, one of the fields is an address field, e.g.
SOQUEL CA 95073 SOUTHAVEN, WA 98671 SOUTHBURY, CT 06488 SPENCER IA 51301 SPOKANE, WA 99201 SPRINGFE VA 22150
I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:
Address field/ Abbreviation field SOQUEL, CA 95073/ CA SOUTHAVEN, WA 98671/ WA SOUTHBURY, CT 06488/ CT SPENCER, IA 51301/ IA SPOKANE, WA 99201/ WA SPRINGFE, VA 22150/ VA
Quick question… I am trying to accomplish something that I believe it is fairly simple. At least in Excel, it is. I have two tables with names. I am trying to create a formula that will compare name in table 1 with a name in table 2. If the names match (exact matches only) it will let me know. Something like =IF (NAME1=NAME2,”TRUE”,”FALSE”) in Excel. I have trying using Iif, but it returns that I cannot divide by zero. I have linked the tables, so if I query NAME 1 and NAME 2, it will only display names on table 1 that are found on table 2. Any help would be appreciated. Thanks,
Hi, I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'. The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table. So simply put the query I want is the opposite of the one I made with the wizard.
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name, description, type and date.
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name (text), description (text), type (text) and date (date). I would always search on all four fields.
I have a form based on a table called checkwriter. Right now it is comprised of multiple dropdowns that work great but now I need a textbox where the user can type in a few characters and have the form display the results which match the first few characters from the field "memo" from the checkwriter table. Im so a newbie to this and not sure whether I need 2 write a seperate query for this or write a simple procedure for that text box. Any help to get me started would be greatly appreciated.
I need to match the data in all the fields between 2 tables.
eg. Field 1 (Table 1) = Field 2 (Table 2) Field 2 (Table 1) = Field 2 (Table 2) so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
This is probably dead simple, but I am brain dead today.
I have two tables: Requests with fields (ID, Cust, Amount, Ref, Date)
and
Actuals with fiels (ID, Cust, Amount, Ref, Date)
Now, if Requests.Ref is null, then update Requests.Ref = Actuals.Ref and Requests.Date = Actuals.Date if and only if there is only one record in Requests and only one record in Actuals where Requests.Cust = Actuals.Cust and Requests.Amount = Actuals.Amount.
Currently, I am just doing an inner join between the two tables, but if there are two requests with a given cust/Amount, but only one Actual, then both Requests will get the same Actual Ref and Date.
I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
