Cross tab forms

[New2VB]

Hi again...

Is it possible to have a cross-tab form?

The kind of thing I'm trying to do looks like

Company Name A B C D
Date 1 1 1 1
Date 2 2 2 2

The concept works fine in a cross-tab query but I can't add information. I have seen hxxp://bytes.com/topic/access/answers/544757-dynamic-crosstab-form-possible, and hxxp://support.microsoft.com/default.aspx?scid=kb;en-us;Q328320 but I am not sure how they apply.

I have attached test02.mdb (many thanks to Poppa Smurf for the help on this) ansd if you open the prices form you may get an idea of what I am trying to do by having each company selectable via a combobox an dbeing able to add records throough the textboxes. In this example I am updating prices on a daily basis but the principle is the same.

 

[vbaInet]

A cross-tab query is not updateable and apart from that you wouldn't be able to create a form from a cross-tab query if the columns are not static.

Your only option would be to perform the calculations in the Control Source of several textbox, as you would do with normal calculations.

 

[New2VB]

Mmmm.. thought as much... but creating 1 unbound combobox and 13 unbound textboxes for each of the 19 customers seems like a PITA, doing it through individual datasheets seems like the way forward

 

[vbaInet]

That means 13 datasheets?

 

[New2VB]

Nope, one listbox that on AfterUpdate enters the correct CompanyNo in a table which is then edited via a subform. Something along the lines of the attached .mdb if you open the company form and use the Pricessubform to edit the daily price changes.

I would like to call up the Prices form directly and be able to edited records for each company directly without going via the Company form.

 

[DCrake]

You actually need a products table for pricing and a company table and create a many to many relationship as many companies can sell the same product but have a different price. As many products can also be sold by the same company.

You do not create a field for each different product, even in that example you would be limited by Acces constraints to the number of fields in a table.

 

[New2VB]

Thanks for the 'products' table info, it has given me an idea about solving a different problem.

I will create a Products table and see what I can do with it....tomorrow...

 

[New2VB]

Hi,

Apologies for the added delay.

Products table created & populated but at the risk of asking a dumb question how do you create a many-many relationship?

 

[vbaInet]

It's simply a junction table i.e. table A -> Junction < Table B between two tables with both tables on either side connected via a one-to-many relationship to the junction to form a many-to-many between themselves (indirectly).

Maybe these will help:

http://www.databasedev.co.uk/many_to_many_example.html
http://support.microsoft.com/kb/304466

 

[New2VB]

Hi vbaInet, nice to you have you here.

OK, I've created the table LinkCompanyPrices as suggested and created a Many-Many relationship with "Company" & "Prices. Wasn't sure if the Link table required a primary key so I didn't set one.

How does this contribute to the original problem of being able to update multiple records from one form?

Changed test_02.mbd attached. Source form = Form1.

 

[vbaInet]

It's good to be here

Unfortunately, there's a problem. Your relationships on the current db and the one before have issues. A table can be further normalised and some links redone. This wouldn't be a one minute fix so I would advise you create a new thread in the Theory and Practice of Database Design section showing a screenshot of your Relationships and just give a brief of what the project is for. That way you can get the structure right before thinking of reconsidering designing your forms.

 

[Poppa Smurf]

I had trouble opening your Company form, an Invalid format error message was displayed.

Anyway to solve your problem of updating you as you found out you cannot do it using a crosstab or a layout similar to your prices form. I used a previous copy of your database and made the follwoing changes.
1. Created a temporary table tbl_temp_prices. This will store the new prices and will be used to update the current prices in the Prices table.
2. Created a new table tbl_products as the current table products had the products as fields.
3. Create a continuous form frm_update_prices this will be used to enter the new price for the product. Select Company and all the products sold by the company will be displayed. Alternatively the form can be designed so that you select a product and all the companies that supply the product will be displayed.
Click on Save and Exit to Close the form and update the prices. Click in Abort and Exit to Close the form. In both cases when the form closes the temporary table is deleted.
4. Created a query qry_update_prices, this is used to update the prices table with the prices in the tbl_temp_prices.

 

[New2VB]

Poppa Smurf

You ARE the man. Excellent.Thank you.

Tried to add to your reputation but got this message... again

"You must spread some Reputation around before giving it to Poppa Smurf again."

Kudos to you anyway...