fill and arrange the text boxes based on the value of them in table

Question

I have a paper printed form that have 11 empty row and I should show the related costs for any subscriber in it. I should fill them based on the related rows from a table. It is easy but there is a problem and need a trick. If I bound this 11 rows in report to related fields in table and all the fields in the table have value and data, there is no problem. But if some of the rows in the table don't have value (for example a subscriber don't have a type of cost) , I cannot show them blank (blank row) in report (printed form). I should hide the row and replace a row that have value instead of it. Shortly, there is no blank row allowed in final printed form. If there is for example 10 row that have value in table, I should show them in 10 row continuously in printed form and I cannot leave blank row between them. I need an flexible report.

How should I do this?

---

Karim Vaziri Regards,

Answer 1

Do you want the report to show four blank rows after the rows with data print as in your drawing?  OR do you want something like this, where the report is based on the query qtblTEST with Criteria for Field2 set to "Is Not Null"?

---

If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

Answer 2

Hi,

I don't need showing blank row after filled rows, and your report is OK.

But there is a problem, it is the mobile phone bills that I should print, and I should print them based on Customer's ID. 

For example, a customer has this costs:

Subscription cost

Internet Cost

Text Cost

Roaming

and another customer don't have for example Internet cost, the text cost that is after it in table should replace the Internet cost in printed form

---

Karim Vaziri Regards,

Answer 3

Please show screen images of what you have tried and results.  This will help us, help you.

---

If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

Answer 4

---

Karim Vaziri Regards,

Answer 5

Your table is currently wrongly designed as it has multiple columns (fields) for the costs.  That 'encodes data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

What you have is a many-to-many relationship type between Subscribers and CostTypes.  This should be modelled by three tables, in broad outline as follows:

Subscribers
….SubscriberID  (PK)
....FirstName
....LastName

CostTypes
….CostTypeID  (PK)
….CostType

The many-to-many relationship type is modelled by a third table which resolves it into two one-to-many relationship types as follows:

SubscriberCosts
….SubscriberID  (FK)
….CostTypeID  (FK)
….CostDate
….Cost

The primary key of this table is a composite one made up of the two foreign key columns, SubscriberID and CostTypeID, and the CostDate column.  The cost column holds the values which you have termed 'data'.

A report would be based on a query which joins the three tables.  As only those cost types for which a subscriber has incurred a cost will be represented by rows in SubscriberCosts, the report will show only those cost types and costs as a continuous list, with no empty rows within or following the set of rows.

You'll find an example of a simple report based on a many-to-many relationship type like this in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, if you select the Open Report option, you'll see how, for each student, only those courses which each student has taken are listed per student.  In this demo students are analogous to your subscribers, courses to your cost types, and status to your cost 'data'.  In your case you don't need a table equivalent to my Statuses table of course, as the Cost column in SubscriberCosts is not a foreign key.

The demo illustrates a number of possible interfaces, this being its primary purpose, but you should use a conventional form/subform interface, which is very easy to set up and requires no code.

---

Ken Sheridan, Stafford, England

Answer 6

Dear Ken,

Thanks for your help, but I have a problem. I know you are right, but I get the subscriber information and their cost information from the mobile provider company as a .CSV format  file and I cannot create several table based on it.

---

Karim Vaziri Regards,

Answer 7

Thanks for your help, but I have a problem. I know you are right, but I get the subscriber information and their cost information from the mobile provider company as a .CSV format  file and I cannot create several table based on it.

You should be able to decompose the imported table into a set of correctly normalized tables, which can then be queried easily.  This is done by executing a set of INSERT INTO statements (append queries) as follows:

1.  Insert a set of distinct subscribers from the source table into a Subscribers table in which the key is an autonumber key.

2.  Insert a set of distinct cost types from the source table into a CostTypes table in which the key is an autonumber key.

3.  By joining the above two tables to the source table on the relevant non-key columns, insert rows into a SubscriberCosts table.  You will need to execute eleven INSERT INTO statements, one for each of the eleven 'data' columns in the imported table.

The above can easily be automated as a one-click operation in a VBA function or procedure.  For an illustration of the methodology see DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file imports non-normalized data from Excel and decomposes it, with an explanation of each step as it is executed.

---

Ken Sheridan, Stafford, England

Answer 8

Dear Ken,

You are right. I made about eleven append query and create a new main table for gathering all data. I append the result of this append queries to main table and after that I extracted the desired data for my subreport from this table.

Thank you for your help.

---

Karim Vaziri Regards,