vlookup type query

[ShogunPatch]

Access newbie here; please be patient (and gentle!).

I have a working but clunky spreadsheet which I am trying to rebuild as a database. In one of my tables I have the following fields (amongst others):
ID, Schedule, Label, Value, Denominator

The Denominator field contains a Schedule and Label combination which uniquely identifies another record in the same table.

I want to build a query which, for each record in the table extracts the Value of its defined Denominator so that I can then divide the value of that record by the value of its Denominator (I may do this calculation in the query itself, or I may export it to Excel).

I'm pretty sure this must be not only possible, but fairly straightforward but must admit I'm struggling. In Excel I would simply use a vlookup formula but I've not so far found the equivalent in Access. Any help on this would be much appreciated.

Many thanks.

 

[Tieval]

Can you make a simple example of this, basically put a few rows of data and explain how they link up.

 

[ShogunPatch]

Hi Tieval, thanks for showing interest in my question.
I imagine there is probably a way to paste table extracts into here but I haven't worked out what that is yet, so in the interest of expediency I've written out a sample below, which I hope clarifies what I mean. My source table currently contains fields 1-5 (ID - Denominator) and what I think I need is a query which will add field 6 (Denominator Value).

ID Schedule Label Value Denominator Denominator Value
12 Food Revenue 100
13 Food Cost of Sales 28 Food Revenue 100
14 Food Payroll 30 Food Revenue 100
15 Food Other Costs 16 Food Revenue 100
16 Beverage Revenue 70
17 Beverage Cost of Sales 19 Beverage Revenue 70
18 Beverage Payroll 25 Beverage Revenue 70
19 Beverage Other Costs 6 Beverage Revenue 70

Many thanks.

 

[ShogunPatch]

Sorry, my example above does read very clearly as I haven't worked out how to layout tables...

 

[ShogunPatch]

D'oh!! OK, I worked out how to attach a sample table in Excel...

 

[Tieval]

You should for starters use more than one table, one with the actions as such and one with denominators.

 

[Minty]

Actually the revenue field itself is calculated so shouldn't really be stored, always calculated from the underlying values.

 

[Tieval]

Well that is actually a good question. It depends on whether you are charging a price and then allocating the money or creating a cost structure and generating prices.

The big question here is what you are trying to do. Once you have established this you can then implement a database structure but as always with databases the purpose is the critical part, followed by a definition of structure which needs a change of thinking from spreadsheet logic.

 

[ShogunPatch]

Well actually, I'm not doing either. The data in my table is uploaded from a set of accounts provided by a client which includes both component parts and totals (and various subtotals, etc). I just receive the accounts in PDF, convert the whole lot to Excel and then upload it into the database, lock, stock and barrel. In any event, while it would be possible to calculate the subtotals and totals, and indeed I do just that for some of my subsequent analysis, in practice it is useful having the subtotals and totals in my table already since they too need to have an allocated denominator and if they were calculated afresh each time I'm not sure how I would be able to define what that was.

I agree that the denominator values should be separated from the main table, but my dataset is too long and complex to do that manually, hence I'm assuming I could do a query to determine those as per the attached sample.

I just need to work out how to build that query (hint hint), and then how to build a further query to link the Denominator value back to each record in my original table (hint hint hint).

 

[Minty]

Your 1st query is simple, if the data is always presented in the way your example is shown.
Open the query designer and add your main table.
Select the first field and type DenominatorTxt: [Schedule] & " " & [Label] in the top empty box

Secondly add the Denominator field and then in the criteria box enter Is Null or ""

Run this query see what you get.

 

[ShogunPatch]

Hhmmm... It may be simple, but I've tried what you suggest and it doesn't work.

This implies one of two obvious conclusions:

1. I'm too dim to understand your simple instructions (not impossible)
2. In providing my simplified example I haven't properly reflected the reality of how my data is structured (I'm hoping that's it...)

I've therefore exported a portion of my actual data table and attach this as Sample 3.

• Sheet1 is my actual data table (albeit labels and numbers have been changed to protect the innocent).
• Sheet2 is a facsimile of what I think Query1 should produce
• Sheet3 is a facsimile of what I think Query2 should produce

Many thanks for your continued interest.

 

[Minty]

It's number 2 as far as I can see. From the sample provided your data doesn't seem to be correlated. The original sample and desired didn't tell us what we needed.
I can't see any direct relationship between the Schedule, Label and ID , and then the Denominator. The bits I can assimilate are that the label is simply the ID with 'Label' stuck in front of it.

Your Initial query can I think be done very simply in the query designer go to SQL view and paste the following then switch to design view, this assumes your main table is called table1

SELECT Table1.Group, Table1.Year, Table1.Period, Table1.Denominator, Sum(Table1.Actual) AS SumOfActual, Sum(Table1.Budget) AS SumOfBudget, Sum(Table1.LY) AS SumOfLY
FROM Table1
GROUP BY Table1.Group, Table1.Year, Table1.Period, Table1.Denominator;

You may want to consider changing the field names as Year, Group, Period, Label all mean something in Access - they are reserved words or are used in describing parts of something in Access and would confuse things later in life.

 

[ShogunPatch]

Minty
I don't really follow the code but it looks like it seems to be working (I need to find time to manually slice and dice my data in Excel to double check); so thanks for that.
Now I just need to work out how to cross reference the output...