Search results

I am almost finished creating this Access 2007 database and will have to split it and load it onto a shared drive. My question is simple: should the split occur AFTER I have created and finished my switchboard menu of items?

I see what you're saying: even though I have fields that are common in all three tables, the fact that one table doesn't have a foreign key in it that matches the primary key of the table I want to join it to, means I'm barking up the wrong tree. Oh boy....this is what happens when you extract...

I don't know if you saw the pdf attachment in my reply spikepl but I did design the 3 tables so that they each relateable fields. Those are the color coded fields in the pdf attachment. But when I queried the first two tables (tblRevenueByDirMgr and tblAssumpByDirMgr) and then queried that...

See attached.

I have 3 tables that I pulled from external sources, none of which came with ID fields or primary keys. I ran a simple sql statement on all three tables to add primary keys. The three tables are: tblRevByDirMgr tblAssumpByDirMgr and tblSFADetailByDirMgr. Each table has many fields that are...

Both fields are left aligned. When I made the qry932 a make-table query then joined the two, both as tables then it worked. Why? Why didn't it allow to build a query on another query in that instance?

If both sets of fields that are being joined are text fields, why is there a data type mismatch error? SELECT qry932_SFADetailAddMRCNRC.DirFirstName, qry932_SFADetailAddMRCNRC.DirLastName, qry932_SFADetailAddMRCNRC.EmployeeNo, qry932_SFADetailAddMRCNRC.MgrFirstName...

Understood

Attached are the final 2 files I am working with

@plog I have attached samples of the root table data and the queries I created based off that table. qry710_AssumpAddedToPastRev is where I seem to be having the most trouble with duplicates. B. What I was expecting was for LDMRR CurrFcstAmt to be around $9,816,000

Yes, that is the case. For instance in the subLastRevenueDates query, one ProdRevTypeMatch-AssumpDate combo has 51 instances. I don't see a way around that though because the query that subLastRevenueDates is built on, is based off a table that is normalized, therefore at its origins fields like...

Why is this query producing duplicates in the ForecastAmount field? SELECT qryAssumptions.AssumpDate, qryAssumptions.ProdRevTypeMatch, [AssumpAmt]+Nz([RevenueAmt]) AS ForecastAmount FROM (qryAssumptions LEFT JOIN sub_LastRevenueDates ON (qryAssumptions.ProdRevTypeMatch =...

Ah! Thanks

I am on the first portion of a long IF Then statement and I can't even get the first portion to work. The error is "wrong number of arguments". Please tell me what I am doing wrong. ProjRevMRC: IIf([Rev Lag]=30 And [ProjRevDate]<=...

I've tried everything I can think of to arrive at my desired results but no dice. Since I have managed to thoroughly confuse you, is it ok if I close out this thread and start a whole new one, keeping my question as simple as possible?

I have to use qry704_AssumpSumGroupedByProdRevType.AssumpDateMinus1 in the JOIN clause instead of qry704_AssumpSumGroupedByProdRevType.AssumpDate due to the fact that Forecast will based on Revenue from the most recent closed month plus Assumption amounts from the PREVIOUS month. So I if I try...

You're right--I found the issue and fixed that. Taking your code and changing field/table names to my actual query, here's what I ran: qry706_subLastRevenueDates SELECT qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch, qry704_AssumpSumGroupedByProdRevType.AssumpDate...

I compressed it. Can you view the attached?

I thought that perhaps you could expand it once you open it; if I try to paste it onto a Word doc to make it larger, it exceeds the attachment size limit for this forum.

Screenshot attached