Newbie VBA quandry here... I cr

[Dijital]

Newbie VBA quandry here...

I created an Access 2000 database linked to a SQL database for reporting purposes.
I have a table with a "Cartesian Product" like so:

computername, qnumber
COMPUTER1, HOTFIX1
COMPUTER1, HOTFIX2
COMPUTER1, HOTFIX3
COMPUTER2, HOTFIX1
COMPUTER2, HOTFIX2
COMPUTER3, HOTFIX1

The Computernames are string values as well as the Qnumbers.

I want to process this table into a more workable model like so:

computername, qnumber1, qnumber2, qnumber3
COMPUTER1, HOTFIX1, HOTFIX2, HOTFIX3
COMPUTER2, HOTFIX1, HOTFIX2
COMPUTER3, HOTFIX1

I would prefer the field namse be = to the string values of the original qnumbers, and the values be TRUE/FALSE. That way as new Qnumbers arrive, they would just generate a new field heading.

Is there a way to do this is VBA for access 2000? I've only done a little VBA and some VBscript...

Thanks for any help!

-Jim Connors

 

[JeremyNYC]

You can do this with a crosstab query, rather easily. There's a wizard when you create a new query that will walk you through the process.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Dijital]

Jeremy,

I did try that, with computernames as the row header, the Qnumbers as column headers. I did a count of the Installed fields. I ended up with a datasheet that looks like what I want.
Q1 Q2 Q3
computer1 1 1 1
computer2 1 1
computer3 1

I ran into one issue though. When I try to run this as a make-table query, I get:
computername - qnumber - count of installed
computer1 q1 1
computer1 q2 1
computer1 q3 1

Basically, back to the same info.
I can export the crosstab to excel, then back to access as a table, but this seems silly; there must be a programmatic way of doing it.
I'll add that I could also create a reference or index of the qnumbers that are relevant.

-Jim Connors

 

[JeremyNYC]

Jim,

Why do you want to do a make table from this? When you make the change in the QBE screen you're going to lose a lot of info, as you've seen.

Why do you need it to be a table?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Dijital]

In short, I need to do reporting. Not just what machines have a patch, but the machines that do not have a patch. Specifically grouped by machine, not patch. Any suggestions on the quer/reporting side to avoid creating a new table?

-Jim

 

[JeremyNYC]

Can you not build the report off the crosstab query?

Can you not change the format of the report so that it the data could be in different rows in your query, thus eliminating the need for a crosstab?

I'm not quite sure why you would need to create a table to do reporting. I've only had to do this once in seven years. If you play with the queries some you should be able to get all the values you need into one query.

Try it in a normal select query and post your sql and describe the report you're looking for. Maybe we can work something out here.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.