field based on value from another field

[vincesavoldi]

Hi all, I am having an issue sorting out some data..
I can do it using multiple queries but I would love to find a way to do it in a single query... Sorry If I am not using proper terms but Being very new at this i have not learned everything yet

I have formulated a couple sample tables to show what my source data looks like and the result I would like to get. (See below)

I have a table with items and pricing. Each item will have multiple lines showing the various prices with another field determining what the price is related to.
I would like to query the table to output a table showing a single item with the various pricings each in a field. To toss another variable into the mix there are multiple sets of data per item based on a dataid field. So the source has , Item,, Type, Price and dataID.
I want to show Item, Price1, Price2,Price3, for a specific itemID.

Source Data
item type price dataID
101 1 5.01 aaa
101 2 6.75 aaa
101 3 9.95 aaa
102 2 7.25 aaa
102 3 13.25 aaa
103 1 19.95 aaa
103 2 7.25 aaa
103 3 8.32 aaa
101 1 5.01 bbb
101 2 6.75 bbb
101 3 9.95 bbb
102 2 7.25 bbb
102 3 13.25 bbb
103 1 19.95 bbb
103 2 7.25 bbb
103 3 8.32 bbb


Resulting data
item price 1 price 2 price 3
101 5.01 6.75 9.95
102 7.25 13.25
103 19.95 7.25 8.32

I would like to filter for only the aaa


It's been driving me crazy trying to figure out how to do it in a single query :banghead:

thanks in advance, Vince

 

[pbaldy]

Have you played with the crosstab query wizard?

 

[vincesavoldi]

Have you played with the crosstab query wizard?

Yes actually I played with it quite a bit and came close but could not seem to filter the dataID correctly. The query would find the 1st instance of Item-type-price and ignore the remaining.

Using 2 queries the crosstab worked beautifully as the 1st query would select only the items with the dataID I need, then the crosstab query reformatted the info to be usable by excel pivot tables or access reports.

I would really love to be able to do then at the same time.

thanks!

 

[pbaldy]

Curious; I'm certainly no expert with crosstabs. Did you make sure the field with the criteria had "Where" in the total row? It might also be in one of the others, like column heading, but I think you want it alone with Where to filter properly.

 

[vincesavoldi]

Curious; I'm certainly no expert with crosstabs. Did you make sure the field with the criteria had "Where" in the total row? It might also be in one of the others, like column heading, but I think you want it alone with Where to filter properly.

I'm not sure how to specify this, I have tried everything that looks like it should work.
I seem to have made something work properly but as soon as I click on make table to create a table it breaks the crosstab. I am sure I am doing something wrong

 

[pbaldy]

Normally I wouldn't expect to make a table, just use the crosstab query for whatever. Why the new table?

 

[vincesavoldi]

Normally I wouldn't expect to make a table, just use the crosstab query for whatever. Why the new table?

Basically I would need the table to generate a report either in access or taking it out to excel for a pivot table or simply for a lookup table.

I totally do not understand how someone would use a crosstab query without taking it to a table.
While the crosstab seems to generate what I am looking for if I can't then take that data and create a sales report form the data it does me no good.

Is there some way to do what I need without the crosstab function or multiple queries?

 

[pbaldy]

A report can be based on a crosstab, I have quite a few (typically you need to handle dynamic field names). I just tested exporting one to Excel and it worked fine.

 

[vincesavoldi]

A report can be based on a crosstab, I have quite a few (typically you need to handle dynamic field names). I just tested exporting one to Excel and it worked fine.

I have no idea how to do that. I am very familiar linking access data into excel, and manually exporting a table is easy but how would one export a crosstab if it is not making a table when you run the query?

The person doing my reporting before myself used to spend many hours in Access to create queries to then create daily reports in Excel (literally 4 hours daily). I have eliminated most of the drudgery by creating a few master tables with all my data and then in excel use pivot tables and lookups to display my reports (down to 20 min to create my dailys).

I am always trying to make things work with less steps and a single macro to create my tables.

 

[pbaldy]

In a very brief test this created an Excel file from a crosstab query:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", "c:\test.xls", True

 

[vincesavoldi]

In a very brief test this created an Excel file from a crosstab query:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", "c:\test.xls", True

Well, thanks for your input, and I can see how the code line works, but I have no idea how to implement that.

I get an error trying to use it as an SQL statement query so i am afraid it is above my knowledge level.

I do appreciate your time and effort trying to help!

 

[pbaldy]

I don't know your process, but in a brief test queries show in Excel when using the "Get External Data", you may be able to get the data that way.