Query not displaying all records (1 Viewer)

Hi all,

I have two tables and require the records to appear when I run a query. One field of from a table called CLI and the other is called Equipment. I require the field AccountNo from the CLI table to correspond with four fields from the Equipment table. The equipment form is a corresponding form but when I run the query it only takes the first record and not all. I have added some screenshots and am hoping someone may be able to help.

Your first two screenshots miss out the most important info
1. The query join isn't clear. Which fields are the two tables joined by and are these PK fields
2. The field headings aren't visible in the query results

Please redo and also post the query SQL.

The fact that you have records with no data at all indicates an probable issue with your query structure

Hi,

Many apologies, I have included some new screenshots.

The last one is all the data it should show under that account number but it is only showing one not all.

There is another question. Can you ever have more than one item of equipment for a given account number in CLI? Because if so, you have the wrong JOIN type. The relationship line in the first of the JPG files says you have a 1:1 match-up, but if one account has many items, you probably want 1:many and that doesn't appear to be what you have.

Another problem is that you must have imported that information in the second JPG from a spreadsheet for which some of the information is implied (i.e. blank, implying the same value as the previous row) rather than explicitly repeated. Anything in this table on a row with an implied value in the first column is NEVER going to show up in a JOIN because there is nothing in such records on which a JOIN could be managed.

If I am right in my inferences, you need to learn a bit more about normalization. Access is not Excel. They behave differently. Excel, despite its ability to perform interesting and powerful computations, is at base a PRESENTATION system. Technically, other than some cross-column or cross-row aggregate functions and some random cross-references, in Excel every cell is independent of all other cells. There is a mind-set in Excel that will not work in Access. Learning about normalization will get you started down that road to breaking the mind-set.

Hi,

Thank you for your response. I will have a look at the relationship in this field then as the account number can have more than one record. The data is not from an excel spreadsheet the form is set to display as a data form. It was previously displayed as a continuous form however I changed it so you could see the data easier.

Let's forget about forms for now and get your query to display what you want. You are going to have to demonstrate this with data for us. Please provide 2 sets of data:

A. Starting data in your tables. Provide table and field names and enough data to cover all cases.

B. Expected data in your query based on A. Show us what data you expect to show up in the query when you feed it the data from A.

Again, data. Show us what you are starting with and expect to end with.

I wasn't looking at the form. I was looking at the 2nd JPG, which is titled Query1. If that is REALLY a query and not a weirdly named form, you have a serious problem for linking records. Queries do not display blank lines unless you have blank lines in the raw tables underneath them, and I am astounded that they would maintain any semblance of order for display purposes. Blank lines cannot be ordered unless there is a hidden ordering number used for sorting but not for display. What does that query look like in Design View?

I don't understand the structure or your input process at this point.