datasheet form with dates in new columns

[Simone]

I need to make a simple datasheet form based on 2 fields "Company name" and "Inspection date". There are multiple inspection dates for each company which are entered in an Inspection form.




The datasheet form should be as follows:
Column1 is company name - column2 is first inspection date - column3 is 2nd inspection date etc for up to 7 dates.


However I cannot get the dates to go into separate columns. I end up with a new row for each new inspection date. How can I get the "Inspection date" field to put the dates into separate columns?


Thanks in advance for your help.

[ranman256]

you shouldnt, that is not good database design.
it should be:
[co], [inspection date]
co Name, date1
co Name, date2
co Name, date3

[isladogs]

If you want this spreadsheet style output create a crosstab query from your table OR use Excel

[Simone]

Thanks for your replies

Since it is bad database design, I really just need this information displayed in a report. Is it possible to create a report whereby the "inspection date" field lists the dates in multiple columns whereby "inspection date 1" is the earliest and "inspection date 4" is the most recent inspection?
Like this:
[co], [inspection date 1], [inspection date 2], [inspection date 3], [inspection date 4]

[Micron]

You could try formatting the control that contains the date to display the data in columns. In design view, select the control, Ribbon > page setup > Columns. You will have to make sure it's only 1 row in height, and set the Can Grow property to No. Variations in the count of dates would mean your columns don't line up with any header labels you might be using. See below for more on that when considering a ct query as well.

Or try basing the report on a cross tab query that was mentioned previously. One problem you could encounter there is that the number of fields in a ct query can change from one time to another. So if you have 7 dates the 1st time and 5 the next, you'll get 7 then 5 date fields. Specifically setting the number of fields in the query might solve that, but why complicate things? Would dates in rows in a report be so bad? It might just come down to how you design the report.